Detailed explanation of the usage of Oracle triggers

2 years ago
William Carter
2 minutes

An Oracle trigger is a stored procedure that automatically executes when specific database operations (such as inserts, updates, deletes) occur. Triggers can be used to enforce data integrity constraints, automatically update related data, log information, and perform other functions.

Here is an example of how to use an Oracle trigger:

Create a table:

CREATE TABLE employee (
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(100),
  salary NUMBER(10, 2),
  commission NUMBER(10, 2),
  total_salary NUMBER(10, 2)
);

Create a trigger to calculate the total salary.

CREATE OR REPLACE TRIGGER calculate_total_salary
BEFORE INSERT OR UPDATE OF salary, commission ON employee
FOR EACH ROW
BEGIN
  :NEW.total_salary := :NEW.salary + :NEW.commission;
END;
/

In this trigger, the BEFORE keyword indicates that the trigger will execute before the operation being triggered, the INSERT and UPDATE keywords specify that the trigger will be activated during insert and update operations. The OF keyword specifies that the trigger is concerned with the columns of salary and commission. The FOR EACH ROW keyword indicates that the trigger’s code will be executed for each row of data.

The trigger code block is written in the PL/SQL language, where :NEW is a special keyword that represents the row to be inserted or updated.

Now, when data is inserted or updated in the employee table, triggers automatically calculate the total salary and store the result in the total_salary column.

INSERT INTO employee (id, name, salary, commission) VALUES (1, 'John Doe', 1000, 200);
UPDATE employee SET salary = 1500 WHERE id = 1;

You can verify the trigger’s effect by querying the table.

SELECT * FROM employee;

ID  NAME      SALARY  COMMISSION  TOTAL_SALARY
--  --------  ------  ----------  ------------
1   John Doe  1500    200         1700

From the examples above, it can be seen that triggers can execute code before or after data operations, allowing access to and modification of the data that will be operated on. This makes triggers a powerful tool that can be used to implement complex business logic.

bannerAds