To create a trigger, use the CREATE TRIGGER statement. By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement. For information about trigger states, see Overview of Triggers.
When using the CREATE TRIGGER statement with an interactive tool, such as SQL*Plus or Enterprise Manager, put a single slash (/) on the last line, as in Example 9-1, which creates a simple trigger for the emp table.
CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (NEW.EMPNO > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :OLD.sal);
dbms_output.put(' New salary: ' || :NEW.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
/
Because the trigger uses the BEFORE keyword, it can access the new values before they go into the table, and can change the values if there is an easily-corrected error by assigning to :NEW.column_name. You might use the AFTER keyword if you want the trigger to query or change the same table, because triggers can only do that after the initial changes are applied and the table is back in a consistent state.
Because the trigger uses the FOR EACH ROW clause, it might be executed multiple times, such as when updating or deleting multiple rows. You might omit this clause if you just want to record the fact that the operation occurred, but not examine the data for each row.
After the trigger is created, following SQL statement fires the trigger once for each row that is updated, in each case printing the new salary, the old salary, and the difference between them:
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10;
The CREATE (or CREATE OR REPLACE) statement fails if any errors exist in the PL/SQL block.
The following sections use Example 9-1 to show how parts of a trigger are specified. For additional examples of CREATE TRIGGER statements, see Examples of Trigger Applications.
Trigger names must be unique with respect to other triggers in the same schema. Trigger names need not be unique with respect to other schema objects, such as tables, views, and subprograms. For example, a table and a trigger can have the same name (however, to avoid confusion, this is not recommended).
A trigger fires based on a triggering statement, which specifies:
Note:
Exactly one table or view can be specified in the triggering statement. If the INSTEAD OF option is used, then the triggering statement must specify a view; conversely, if a view is specified in the triggering statement, then only the INSTEAD OF option can be used.
In Example 9-1, the PRINT_SALARY_CHANGES trigger fires after any DELETE, INSERT, or UPDATE on the emp table. Any of the following statements trigger the PRINT_SALARY_CHANGES trigger:
DELETE FROM emp; INSERT INTO emp VALUES ( ... ); INSERT INTO emp SELECT ... FROM ... ; UPDATE emp SET ... ;
Do Import and SQL*Loader Fire Triggers?
INSERT triggers fire during SQL*Loader conventional loads. (For direct loads, triggers are disabled before the load.)
The IGNORE parameter of the IMP statement determines whether triggers fire during import operations:
An UPDATE statement might include a list of columns. If a triggering statement includes a column list, the trigger fires only when one of the specified columns is updated. If a triggering statement omits a column list, the trigger fires when any column of the associated table is updated. A column list cannot be specified for INSERT or DELETE triggering statements.
The previous example of the PRINT_SALARY_CHANGES trigger can include a column list in the triggering statement. For example:
... BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp ...
Note:
Note:
This topic applies only to simple triggers. For the options of compound triggers, see Compound Triggers.
The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to fire the trigger body in relation to the triggering statement that is being run. In a CREATE TRIGGER statement, the BEFORE or AFTER option is specified just before the triggering statement. For example, the PRINT_SALARY_CHANGES trigger in the previous example is a BEFORE trigger.
In general, you use BEFORE or AFTER triggers to achieve the following results:
An AFTER row trigger fires when the triggering statement results in ORA-2292.
Note:
BEFORE row triggers are slightly more efficient than AFTER row triggers. With AFTER row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with BEFORE row triggers, the data blocks must be read only once for both the triggering statement and the trigger.
If an UPDATE or DELETE statement detects a conflict with a concurrent UPDATE, then the database performs a transparent ROLLBACK to SAVEPOINT and restarts the update. This can occur many times before the statement completes successfully. Each time the statement is restarted, the BEFORE statement trigger fires again. The rollback to savepoint does not undo changes to any package variables referenced in the trigger. Include a counter variable in your package to detect this situation.
A relational database does not guarantee the order of rows processed by a SQL statement. Therefore, do not create triggers that depend on the order in which rows are processed. For example, do not assign a value to a global package variable in a row trigger if the current value of the global variable is dependent on the row being processed by the row trigger. Also, if global package variables are updated within a trigger, then it is best to initialize those variables in a BEFORE statement trigger.
When a statement in a trigger body causes another trigger to fire, the triggers are said to be cascading. The database allows up to 32 triggers to cascade at simultaneously. You can limit the number of trigger cascades by using the initialization parameter OPEN_CURSORS, because a cursor must be opened for every execution of a trigger.
Although any trigger can run a sequence of operations either inline or by invoking subprograms, using multiple triggers of the same type allows the modular installation of applications that have triggers on the same tables.
Each subsequent trigger sees the changes made by the previously fired triggers. Each trigger can see the old and new values. The old values are the original values, and the new values are the current values, as set by the most recently fired UPDATE or INSERT trigger.
The database executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on the same table, and the order in which they execute is important, use the FOLLOWS clause. Without the FOLLOWS clause, the database chooses an arbitrary, unpredictable order.
Note:
INSTEAD OF triggers can be defined only on views, not on tables.
An updatable view is one that lets you perform DML on the underlying table. Some views are inherently updatable, but others are not because they were created with one or more of the constructs listed in Views that Require INSTEAD OF Triggers.
Any view that contains one of those constructs can be made updatable by using an INSTEAD OF trigger. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. These triggers are invoked INSTEAD OF triggers because, unlike other types of triggers, the database fires the trigger instead of executing the triggering statement. The trigger must determine what operation was intended and perform UPDATE, INSERT, or DELETE operations directly on the underlying tables.
With an INSTEAD OF trigger, you can write normal UPDATE, INSERT, and DELETE statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place.
INSTEAD OF triggers can only be activated for each row.
Note:
A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs:
If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
INSTEAD OF triggers provide the means to modify object view instances on the client-side through OCI calls.
To modify an object materialized by an object view in the client-side object cache and flush it back to the persistent store, you must specify INSTEAD OF triggers, unless the object view is modifiable. If the object is read only, then it is not necessary to define triggers to pin it.
INSTEAD OF triggers can also be created over nested table view columns. These triggers provide a way of updating elements of the nested table. They fire for each nested table element being modified. The row correlation variables inside the trigger correspond to the nested table element. This type of trigger also provides an additional correlation name for accessing the parent row that contains the nested table being modified.
Note:
These triggers:
For example, consider a department view that contains a nested table of employees.
CREATE OR REPLACE VIEW Dept_view AS
SELECT d.Deptno, d.Dept_type, d.Dname,
CAST (MULTISET ( SELECT e.Empno, e.Empname, e.Salary)
FROM emp e
WHERE e.Deptno = d.Deptno) AS Amp_list_ Emplist
FROM dept d;
The CAST (MULTISET) operator creates a multiset of employees for each department. To modify the emplist column, which is the nested table of employees, define an INSTEAD OF trigger over the column to handle the operation.
The following example shows how an insert trigger might be written:
CREATE OR REPLACE TRIGGER Dept_emplist_tr
INSTEAD OF INSERT ON NESTED TABLE Emplist OF Dept_view
REFERENCING NEW AS Employee
PARENT AS Department
FOR EACH ROW
BEGIN
-- Insert on nested table translates to insert on base table:
INSERT INTO emp VALUES (:Employee.Empno,
:Employee.Ename,:Employee.Sal, :Department.Deptno);
END;
Any INSERT into the nested table fires the trigger, and the emp table is filled with the correct values. For example:
INSERT INTO TABLE (SELECT d.Emplist FROM Dept_view d WHERE Deptno = 10) VALUES (1001, 'John Glenn', 10000);
The :department.deptno correlation variable in this example has the value 10.
Note:
You might need to set up the following data structures for this example to work:
CREATE TABLE Project_tab ( Prj_level NUMBER, Projno NUMBER, Resp_dept NUMBER); CREATE TABLE emp ( Empno NUMBER NOT NULL, Ename VARCHAR2(10), Job VARCHAR2(9), Mgr NUMBER(4), Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(7,2), Deptno NUMBER(2) NOT NULL); CREATE TABLE dept ( Deptno NUMBER(2) NOT NULL, Dname VARCHAR2(14), Loc VARCHAR2(13), Mgr_no NUMBER, Dept_type NUMBER);
The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view.
CREATE OR REPLACE VIEW manager_info AS
SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level, p.projno
FROM emp e, dept d, Project_tab p
WHERE e.empno = d.mgr_no
AND d.deptno = p.resp_dept;
CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n -- new manager information
FOR EACH ROW
DECLARE
rowcnt number;
BEGIN
SELECT COUNT(*) INTO rowcnt FROM emp WHERE empno = :n.empno;
IF rowcnt = 0 THEN
INSERT INTO emp (empno,ename) VALUES (:n.empno, :n.ename);
ELSE
UPDATE emp SET emp.ename = :n.ename WHERE emp.empno = :n.empno;
END IF;
SELECT COUNT(*) INTO rowcnt FROM dept WHERE deptno = :n.deptno;
IF rowcnt = 0 THEN
INSERT INTO dept (deptno, dept_type)
VALUES(:n.deptno, :n.dept_type);
ELSE
UPDATE dept SET dept.dept_type = :n.dept_type
WHERE dept.deptno = :n.deptno;
END IF;
SELECT COUNT(*) INTO rowcnt FROM Project_tab
WHERE Project_tab.projno = :n.projno;
IF rowcnt = 0 THEN
INSERT INTO Project_tab (projno, prj_level)
VALUES(:n.projno, :n.prj_level);
ELSE
UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level
WHERE Project_tab.projno = :n.projno;
END IF;
END;
The actions shown for rows being inserted into the MANAGER_INFO view first test to see if appropriate rows already exist in the base tables from which MANAGER_INFO is derived. The actions then insert new rows or update existing rows, as appropriate. Similar triggers can specify appropriate actions for UPDATE and DELETE.
Note:
This topic applies only to simple triggers. For the options of compound triggers, see Compound Triggers.
The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW, then the trigger fires once for each row of the table that is affected by the triggering statement. The absence of the FOR EACH ROW option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement.
For example, assume that the table Emp_log was created as follows:
CREATE TABLE Emp_log ( Emp_id NUMBER, Log_date DATE, New_salary NUMBER, Action VARCHAR2(20));
Then, define the following trigger:
CREATE OR REPLACE TRIGGER Log_salary_increase
AFTER UPDATE ON emp
FOR EACH ROW
WHEN (NEW.Sal > 1000)
BEGIN
INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
VALUES (:NEW.Empno, SYSDATE, :NEW.SAL, 'NEW SAL');
END;
Then, you enter the following SQL statement:
UPDATE emp SET Sal = Sal + 1000.0 WHERE Deptno = 20;
If there are five employees in department 20, then the trigger fires five times when this statement is entered, because five rows are affected.
The following trigger fires only once for each UPDATE of the emp table:
CREATE OR REPLACE TRIGGER Log_emp_update
AFTER UPDATE ON emp
BEGIN
INSERT INTO Emp_log (Log_date, Action)
VALUES (SYSDATE, 'emp COMMISSIONS CHANGED');
END;
The statement level triggers are useful for performing validation checks for the entire statement.
Optionally, a trigger restriction can be included in the definition of a row trigger by specifying a Boolean SQL expression in a WHEN clause.
Note:
A WHEN clause cannot be included in the definition of a statement trigger.
If included, then the expression in the WHEN clause is evaluated for each row that the trigger affects.
If the expression evaluates to TRUE for a row, then the trigger body executes on behalf of that row. However, if the expression evaluates to FALSE or NOT TRUE for a row (unknown, as with nulls), then the trigger body does not execute for that row. The evaluation of the WHEN clause does not have an effect on the execution of the triggering SQL statement (in other words, the triggering statement is not rolled back if the expression in a WHEN clause evaluates to FALSE).
For example, in the PRINT_SALARY_CHANGES trigger, the trigger body is not run if the new value of Empno is zero, NULL, or negative. In more realistic examples, you might test if one column value is less than another.
The expression in a WHEN clause of a row trigger can include correlation names, which are explained later. The expression in a WHEN clause must be a SQL expression, and it cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.
Note:
You cannot specify the WHEN clause for INSTEAD OF triggers.
A compound trigger can fire at more than one timing point.
The compound trigger makes it easier to program an approach where you want the actions you implement for the various timing points to share common data. To achieve the same effect with simple triggers, you had to model the common state with an ancillary package. This approach was both cumbersome to program and subject to memory leak when the triggering statement caused an error and the after-statement trigger did not fire.
A compound trigger has an optional declarative part and a section for each of its timing points (see Example 9-2). All of these sections can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.
SQL> CREATE OR REPLACE TRIGGER compound_trigger 2 FOR UPDATE OF salary ON employees 3 COMPOUND TRIGGER 4 5 -- Declarative part (optional) 6 -- Variables declared here have firing-statement duration. 7 threshold CONSTANT SIMPLE_INTEGER := 200; 8 9 BEFORE STATEMENT IS 10 BEGIN 11 NULL; 12 END BEFORE STATEMENT; 13 14 BEFORE EACH ROW IS 15 BEGIN 16 NULL; 17 END BEFORE EACH ROW; 18 19 AFTER EACH ROW IS 20 BEGIN 21 NULL; 22 END AFTER EACH ROW; 23 24 AFTER STATEMENT IS 25 BEGIN 26 NULL; 27 END AFTER STATEMENT; 28 END compound_trigger; 29 / Trigger created. SQL>
Two common reasons to use compound triggers are:
A compound trigger has a declarative part and at least one timing-point section. It cannot have multiple sections for the same timing point.
The optional declarative part (the first part) declares variables and subprograms that timing-point sections can use. When the trigger fires, the declarative part executes before any timing-point sections execute. Variables and subprograms declared in this section have firing-statement duration.
A compound trigger defined on a view has an INSTEAD OF EACH ROW timing-point section, and no other timing-point section.
A compound trigger defined on a table has one or more of the timing-point sections described in Table 9-1. Timing-point sections must appear in the order shown in Table 9-1. If a timing-point section is absent, nothing happens at its timing point.
A timing-point section cannot be enclosed in a PL/SQL block.
Table 9-1 summarizes the timing point sections of a compound trigger that can be defined on a table.
| Timing Point | Section |
|---|---|
|
Before the triggering statement executes |
BEFORE STATEMENT |
|
After the triggering statement executes |
AFTER STATEMENT |
|
Before each row that the triggering statement affects |
BEFORE EACH ROW |
|
After each row that the triggering statement affects |
AFTER EACH ROW |
Any section can include the functions Inserting, Updating, Deleting, and Applying.
The triggering statement of a compound trigger must be a DML statement.
If the triggering statement affects no rows, and the compound trigger has neither a BEFORE STATEMENT section nor an AFTER STATEMENT section, the trigger never fires.
It is when the triggering statement affects many rows that a compound trigger has a performance benefit. This is why it is important to use the BULK COLLECT clause with the FORALL statement. For example, without the BULK COLLECT clause, a FORALL statement that contains an INSERT statement simply performs a single-row insertion operation many times, and you get no benefit from using a compound trigger. For more information about using the BULK COLLECT clause with the FORALL statement, see Using FORALL and BULK COLLECT Together.
If the triggering statement of a compound trigger is an INSERT statement that includes a subquery, the compound trigger retains some of its performance benefit. For example, suppose that a compound trigger is triggered by the following statement:
INSERT INTO Target
SELECT c1, c2, c3
FROM Source
WHERE Source.c1 > 0
For each row of Source whose column c1 is greater than zero, the BEFORE EACH ROW and AFTER EACH ROW sections of the compound trigger execute. However, the BEFORE STATEMENT and AFTER STATEMENT sections each execute only once (before and after the INSERT statement executes, respectively).
Scenario: You want to record every change to hr.employees.salary in a new table, employee_salaries. A single UPDATE statement will update many rows of the table hr.employees; therefore, bulk-inserting rows into employee.salaries is more efficient than inserting them individually.
Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-3. You do not need a BEFORE STATEMENT section to initialize idx or salaries, because they are state variables, which are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).
CREATE TABLE employee_salaries (
employee_id NUMBER NOT NULL,
change_date DATE NOT NULL,
salary NUMBER(8,2) NOT NULL,
CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
REFERENCES employees (employee_id)
ON DELETE CASCADE)
/
CREATE OR REPLACE TRIGGER maintain_employee_salaries
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
-- Declarative Part:
-- Choose small threshhold value to show how example works:
threshhold CONSTANT SIMPLE_INTEGER := 7;
TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
salaries salaries_t;
idx SIMPLE_INTEGER := 0;
PROCEDURE flush_array IS
n CONSTANT SIMPLE_INTEGER := salaries.count();
BEGIN
FORALL j IN 1..n
INSERT INTO employee_salaries VALUES salaries(j);
salaries.delete();
idx := 0;
DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
END flush_array;
-- AFTER EACH ROW Section:
AFTER EACH ROW IS
BEGIN
idx := idx + 1;
salaries(idx).employee_id := :NEW.employee_id;
salaries(idx).change_date := SYSDATE();
salaries(idx).salary := :NEW.salary;
IF idx >= threshhold THEN
flush_array();
END IF;
END AFTER EACH ROW;
-- AFTER STATEMENT Section:
AFTER STATEMENT IS
BEGIN
flush_array();
END AFTER STATEMENT;
END maintain_employee_salaries;
/
/* Increase salary of every employee in department 50 by 10%: */
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 50
/
/* Wait two seconds: */
BEGIN
DBMS_LOCK.SLEEP(2);
END;
/
/* Increase salary of every employee in department 50 by 5%: */
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 50
/
You can use compound triggers to avoid the mutating-table error (ORA-04091) described in Trigger Restrictions on Mutating Tables.
Scenario: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department. This rule must be enforced by a trigger.
Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-4. The state variables are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).
CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
Ten_Percent CONSTANT NUMBER := 0.1;
TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;
Avg_Salaries Salaries_t;
TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;
Department_IDs Department_IDs_t;
TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE
INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;
BEFORE STATEMENT IS
BEGIN
SELECT AVG(e.Salary), NVL(e.Department_ID, -1)
BULK COLLECT INTO Avg_Salaries, Department_IDs
FROM Employees e
GROUP BY e.Department_ID;
FOR j IN 1..Department_IDs.COUNT() LOOP
Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :NEW.Salary - :Old.Salary >
Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
THEN
Raise_Application_Error(-20000, 'Raise too big');
END IF;
END AFTER EACH ROW;
END Check_Employee_Salary_Raise;
