Triggers (8-9) |
Introduction to Oracle SQL & PL/SQL
Updated 12/3/2001 |
- Setup
- Concepts
- Trigger Structure / Syntax
- Conditional Predicates
- Statement triggers vs. Row triggers
- Trigger timing
- Row trigger concepts
- Uses of triggers
- Privileges
- Managing triggers
Setup
Concepts
A trigger is an block of PL/SQL which is invoked implicitly whenever a triggering action occurs. For example, a trigger could fire whenever any user attempts to insert data into a specific table. In this example, a trigger associates a block of PL/SQL with an DML statement on the EMP table.
CREATE OR REPLACE TRIGGER secure_emp_tr BEFORE INSERT ON emp BEGIN IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR (TO_CHAR(sysdate,'HH24') NOT BETWEEN '08' AND '16') THEN RAISE_APPLICATION_ERROR (-20500, 'You may only insert into EMP during normal hours.'); END IF; END; /
Whenever any user attempts to insert data into the EMP table, this block of code will be executed. If it is not during working hours, the trigger will raise an application error, and the insert will fail.
Trigger Structure / Syntax
CREATE OR REPLACE TRIGGER secure_emp_tr BEFORE INSERT ON emp BEGIN IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR (TO_CHAR(sysdate,'HH24') NOT BETWEEN '08' AND '16') THEN RAISE_APPLICATION_ERROR (-20500, 'You may only insert into EMP during normal hours.'); END IF; END; / |
Trigger timing Triggering event |
Conditional predicates
You can reference the conditional predicates INSERTING, DELETING, and UPDATING to determine what kind of the statement fired the trigger. The conditional predicates behave like a Boolean constant within the trigger body.
The example above is extended to use conditional predicates. (Note that like an anonymous block, the trigger body begins with the DECLARE keyword.)
Statement triggers vs. Row triggers
A DML trigger is either a statement trigger or a row trigger. Statement triggers fire once for the triggering statement. Row triggers fire once for each affected row. By default, triggers are statement triggers. (The triggers defined above were statement triggers.)
You can specify a row trigger with the clause FOR EACH ROW.
CREATE OR REPLACE TRIGGER audit_emp_tr AFTER DELETE OR INSERT OR UPDATE ON EMP FOR EACH ROW DECLARE v_msg VARCHAR2(10); BEGIN IF DELETING THEN v_msg:='DELETE'; ELSIF UPDATING THEN v_msg:='UPDATE'; ELSE --Must be INSERTING v_msg:='INSERT'; END IF; INSERT INTO audit_emp (user_name, action, action_ts, empno, old_sal, new_sal) VALUES (USER, v_msg, TO_CHAR(SYSDATE,'MM/DD HH24:MI'), :old.empno, :old.sal, :new.sal); END; /
This trigger will fire for each row that is affected by the DML statement. If the DML statement affects one row, the trigger fires once. If it affects 4 rows, the trigger fires 4 times.
SQL> INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (1000,'GATES','CIO',7839, SYSDATE, 4500, 0, 10); 1 row created. SQL> SELECT * FROM audit_emp; USER_NAME ACTION ACTION_TS EMPNO OLD_SAL NEW_SAL ---------- ---------- ------------ ---------- ---------- ---------- TEST_USER INSERT 07/09 17:05 4500 SQL> UPDATE emp SET sal=sal*1.1 WHERE deptno=10; 4 rows updated. SQL> SELECT * FROM audit_emp; USER_NAME ACTION ACTION_TS EMPNO OLD_SAL NEW_SAL ---------- ---------- ------------ ---------- ---------- ---------- TEST_USER INSERT 07/09 17:05 4500 TEST_USER UPDATE 07/09 17:06 7839 5000 5500 TEST_USER UPDATE 07/09 17:06 7782 2450 2695 TEST_USER UPDATE 07/09 17:06 7934 1300 1430 TEST_USER UPDATE 07/09 17:06 1000 4500 4950
Trigger Timing
Keep in mind, each DML statement can fire four distinct types of triggers:
- Before Statement
- Before Row
- After Row
- After Statement
The following set of triggers prints results whenever it fires.
CREATE OR REPLACE TRIGGER trigger_timing_bs BEFORE DELETE OR INSERT OR UPDATE ON emp DECLARE v_msg VARCHAR2(10); BEGIN IF DELETING THEN v_msg:='DELETE'; ELSIF UPDATING THEN v_msg:='UPDATE'; ELSE --Must be INSERTING v_msg:='INSERT'; END IF; DBMS_OUTPUT.PUT_LINE('Before statement ['||v_msg||']'); END; / CREATE OR REPLACE TRIGGER trigger_timing_as AFTER DELETE OR INSERT OR UPDATE ON emp BEGIN DBMS_OUTPUT.PUT_LINE('After statement'); END; / CREATE OR REPLACE TRIGGER trigger_timing_br BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(' Before row: Old sal='||:old.sal||' New sal='||:new.sal); END; / CREATE OR REPLACE TRIGGER trigger_timing_ar AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(' After row: Old sal='||:old.sal||' New sal='||:new.sal); END; /SQL> UPDATE emp SET sal=sal*1.1 WHERE job='CLERK'; Before statement [UPDATE] Before row: Old sal=950 New sal=1045 After row: Old sal=950 New sal=1045 Before row: Old sal=800 New sal=880 After row: Old sal=800 New sal=880 Before row: Old sal=1100 New sal=1210 After row: Old sal=1100 New sal=1210 Before row: Old sal=1430 New sal=1573 After row: Old sal=1430 New sal=1573 After statement 4 rows updated.
Also keep in mind that statement triggers will fire even if no rows are affected.
SQL> UPDATE emp SET sal=sal*1.1 WHERE job='DOCTOR'; Before statement [UPDATE] After statement 0 rows updated.
You can have more than one trigger with the same timing. For example, two triggers may fire BEFORE INSERT ON EMP FOR EACH ROW. You have no control over which order Oracle fires multiple triggers with the same timing.
Row trigger concepts
NEW and OLD
Row triggers have access to the the values of the affected row before and after the triggering statement is applied. Examine the triggering statement in the example above:
UPDATE emp SET sal=sal*1.1 WHERE deptno=10;
Every DML statement can be thought of as an action (UPDATE SET sal=sal*1.1) applied to a set of rows (emp WHERE deptno=10).
 |
When Oracle interprets a DML statement it first assembles the set and iterates over the set applying changes to all rows in the set.
 |
If it reaches the end of the set without error, then the statement completes and the changes are applied to the database. This ensures that each DML statement is atomic, meaning that each statement succeeds, or fails, but never aborts in the middle of the operation (which would leave the data in an inconsistent state).
 |
Row triggers have access to the values in each row before and after the statement is applied to that row. First Oracle creates bind variable records inside the server memory; they are called NEW and OLD. The structure of these records is based on the attached table (in our example, the emp table). As Oracle iterates over each row in the result set, it loads the original values of the row into the both OLD and NEW records, and then applies the statement action (SET sal=sal*1.1) to the NEW record.
 |
When the trigger executes, it can access both the old and new values through
these records. From the example above:
(Note that inside the body of the trigger, NEW and OLD must be prefixed with a
":" because the trigger body treats them as a bind variable.)
INSERT INTO audit_emp (user_name, action, action_ts,
empno, old_sal, new_sal)
VALUES (USER, v_msg,
TO_CHAR(SYSDATE,'MM/DD HH24:MI'),
:old.empno, :old.sal, :new.sal);
When the statement is applied to the database, values are copied from the NEW record to the row in the table.
Now recall that a trigger could occur BEFORE or AFTER the triggering statement. In the case of row triggers, both the before and after triggers can reference the OLD and NEW values. In addition, the before row trigger can assign values to the NEW record.
Again, when the statement is applied to the database, values are copied from the NEW record to the row in the table. So you can directly assign new values using the NEW record.
When using NEW and OLD records keep in mind:
- You can only reference OLD and NEW from row triggers, not statement triggers.
- You can only assign values to NEW. Attempting to assign value to OLD will not compile.
- You can only assign values BEFORE the statement. Assigning values AFTER the statement will not compile.
- You can refer to OLD and NEW for any row trigger. The OLD values of an INSERTED record are NULL. The NEW values of a DELETED record are NULL. (You could assign NEW values to a deleted record, but it wouldn't do anything.)
Do NOT modify values like this:
IF :NEW.job='SALESMAN' THEN --Only salesman get commission IF (:OLD.comm IS NULL AND :NEW.comm IS NULL) THEN --all salespeople should have non-null commission UPDATE emp --No no no! SET comm=0 WHERE empno=:NEW.empno; ELSE --if existing emp, adjust from old comm based on raise UPDATE emp --No no no! SET comm=:OLD.comm * (:NEW.sal/:OLD.sal) WHERE empno=:NEW.empno; END IF; END IF;
This misses the point of using NEW and OLD. It also raises the specter of recursive triggers.
Recursive triggers
If a trigger contains a DML statement, it may cause another trigger fire, which may cause another trigger to fire, and so on... This is called cascading triggers, and it should be avoided because it becomes very difficult to debug. The simplest form of a cascading trigger is a recursive trigger; recursive triggers usually fail.
Recursive triggers contain a DML statement in the body which causes the trigger to fire again. Here is a simple recursive trigger.
CREATE OR REPLACE TRIGGER recursive_tr AFTER INSERT OR UPDATE ON emp BEGIN UPDATE emp --No no no! SET sal=sal*1.1 WHERE empno=1000; END; /
The problem here is that when the EMP table is updated, it will fire this trigger which will update the EMP table, which will fire this trigger, which will update the emp table, ... and so on until Oracle runs out of memory. (Usually the trigger will fire 50 times before failing; the actual number depends on parameters set by the DBA.)
WHEN clause
Triggers are often designed to fire only when the row contains specific values. In our previous example, the trigger body do nothing unless the new job title is SALESMAN.
CREATE OR REPLACE TRIGGER derive_comm_tr BEFORE INSERT OR UPDATE OF sal, job ON emp FOR EACH ROW BEGIN IF :NEW.job='SALESMAN' THEN --Only salesman get commission IF (:OLD.comm IS NULL AND :NEW.comm IS NULL) THEN --all salespeople should have non-null commission :NEW.comm:=0; ELSE --if existing emp, adjust from old comm based on raise :NEW.comm:=:OLD.comm * (:NEW.sal/:OLD.sal); END IF; END IF; END; /
Oracle provides a more efficient way to accomplish this when using row triggers: the WHEN clause. The WHEN clause contains a condition which is evaluated for each row. The trigger is fired is fired only when the condition is true.
CREATE OR REPLACE TRIGGER derive_comm_tr BEFORE INSERT OR UPDATE OF sal, job ON emp FOR EACH ROW WHEN (NEW.job='SALESMAN') BEGIN --Only salesman get commission IF (:OLD.comm IS NULL AND :NEW.comm IS NULL) THEN --all salespeople should have non-null commission :NEW.comm:=0; ELSE --if existing emp, adjust from old comm based on raise :NEW.comm:=:OLD.comm * (:NEW.sal/:OLD.sal); END IF; END; /
Note that references to OLD and NEW in the WHEN clause are not prefixed with ':'.
REFERENCING clause
Triggers can access the old and new values of a row through the records named OLD and NEW. You can specify alternate names using the REFERENCING clause. (This sometimes improves clarity of the code. It is rarely necessary.)
Mutating tables
DML statements are supposed to guarantee atomicty. That is, the statement executes all the way, or not at all, and there is no in-between state. Since row triggers are fired for each affected row, row level triggers have the unique ability to look "inside" a statement, to the state of the data after the statement has been issued but before the statement has completed. If that ability were not restricted in some way, it would violate the atomicty of the statement, because at any point we might be seeing the data partway through the statement. To ensure atomicity, row level triggers can see the old and new values for a particular row, but they can only see the values in that row. A row level triggers cannot read from its attached table with a select statement because it would violate atomicity.
- Row level triggers cannot read from the attached table. (Because it would violate atomicity.)
- Row level triggers cannot insert or update primary or unique keys on the attached table. (Because it would have to read from the table to verify the unique constraint, which violates rule above.)
- In versions of Oracle prior to 8i, row level triggers could not insert, update, or delete from tables which were foreign keyed to the attached table. (Because it would have to read from the table to verify the fk constraint.)
If you attempt to read from the attached table, you will receive a mutating table error message. This indicates that your trigger is not designed correctly. Unfortunately, Oracle will not catch this error at compile time. The error will appear at run time and will prevent the associated DML statement from succeeding.
To illustrate the problem of mutating tables, let's say we want to track the highest salary for each department. We'll add a column to the DEPT table to track this. This means that the table EMP will require a trigger to fire each time an employee is inserted, deleted, or a salary is updated. Each time the trigger fired, it should update the salaries for all affected departments. Your first approach might look something like this:
ALTER TABLE dept ADD (hi_sal NUMBER(8,2)); CREATE OR REPLACE TRIGGER hi_sal_tr AFTER INSERT OR DELETE OR UPDATE OF sal ON emp FOR EACH ROW DECLARE -- Initializating with NVL ensures we get a non NULL -- value reagrdless of whether we're inserting -- or deleting v_deptno dept.deptno%TYPE:=NVL(:NEW.deptno,:OLD.deptno); BEGIN UPDATE dept SET hi_sal=(SELECT MAX(sal) FROM emp WHERE deptno=v_deptno) WHERE deptno=v_deptno; END; /
This trigger compiles without error. But when it's invoked, I get the following error message:
SQL> DELETE FROM emp WHERE empno=1000; DELETE FROM emp WHERE empno=1000 * ERROR at line 1: ORA-04091: table TEST_USER.EMP is mutating, trigger/function may not see it ORA-06512: at "TEST_USER.HI_SAL_TR", line 7 ORA-04088: error during execution of trigger 'TEST_USER.HI_SAL_TR'
This limitation cannot be completely overcome, but it's impact can be mitigated through the use of statement level triggers. Statement level triggers execute before or after the statement and so statement triggers are never vulnerable to mutating table error. Therefore, you could use a less efficient approach and re-calculate high salaries for all departments like so:
CREATE OR REPLACE TRIGGER hi_sal_tr AFTER INSERT OR DELETE OR UPDATE OF sal ON emp BEGIN FOR dept_rec IN (SELECT * FROM dept) LOOP UPDATE dept SET hi_sal=(SELECT MAX(sal) FROM emp WHERE deptno=dept_rec.deptno) WHERE deptno=dept_rec.deptno; END LOOP; END; /
This trigger compiles. When invoked, it correctly populates the new column.
SQL> DELETE FROM emp WHERE empno=1000; 1 row deleted.SQL> SELECT * FROM dept; DEPTNO DNAME LOC HI_SAL ---------- -------------- ------------- ---------- 10 ACCOUNTING NEW YORK 5500 20 RESEARCH DALLAS 3000 30 SALES CHICAGO 2850 40 OPERATIONS BOSTON
This approach works for the emp table because there are few departments and few records. However the performance would degrade quickly as we added employees and department numbers. A faster but more more complex approach is outlined below:
- Create a package which contains a PL/SQL table of deptno.
- Create a before statement trigger which empties the table.
- Create an after row trigger which adds a deptno value to the PL/SQL table for each department affected.
- Create an after statement trigger which iterates over the PL/SQL table and re-calculates salaries for affected departments.
While it's certainly more complex, this is an efficient approach to this problem.
Uses of triggers
Enforcing business rules
The declarative constraints should be used as often as possible, but the declarative constraints can only accommodate relative simple business logic. Handling your complex business logic with triggers ensures you won't leave your data integrity to your application design alone.
Enforce Security
CREATE OR REPLACE TRIGGER secure_emp_tr BEFORE INSERT ON emp BEGIN IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR (TO_CHAR(sysdate,'HH24') NOT BETWEEN '08' AND '16') THEN RAISE_APPLICATION_ERROR (-20500, 'You may only insert into EMP during normal hours.'); END IF; END; /Validate transactions
CREATE OR REPLACE TRIGGER hiredate_tr BEFORE INSERT OR UPDATE ON emp FOR EACH ROW BEGIN IF :NEW.hiredate>SYSDATE THEN RAISE_APPLICATION_ERROR(-20500,'Hiredate cannot be in future.'); END IF; END; /
Deriving values and de-normalization
Occasionally triggers are used to derive the value of one column from others. This trigger calculates the highest salary in each department and modifies data in the dept table accordingly.
CREATE OR REPLACE TRIGGER hi_sal_tr AFTER INSERT OR DELETE OR UPDATE OF sal ON emp BEGIN FOR dept_rec IN (SELECT * FROM dept) LOOP UPDATE dept SET hi_sal=(SELECT MAX(sal) FROM emp WHERE deptno=dept_rec.deptno) WHERE deptno=dept_rec.deptno; END LOOP; END; /
Deriving and storing values is sometimes known as de-normalization because you end up representing the same data twice in the database. I'm not a purist on de-normalization. Because the RDBMS of today are not as fast or well organized as database developers would like, sometimes de-normalization a reasonable compromise. But it is a compromise: whatever you gain in speed or simplicity, you often end up losing somewhere else in the data model or application architecture. In the example above performance quickly degrades as the number of rows in the EMP table increases. And in the optimized solution described above (in the Mutating tables section above), performance is robust; but with the addition of three triggers and a package specification, the architecture becomes much more complex.
Another use of derived values is the enforcement of default column values. Consider that a default column value is used when a column is omitted from the insert list. But a user could always specify a new value, overriding the default. Sometimes, you want to data to be derived regardless of what the user specifies. The following example adds a timestamp field in the EMP table and attaches a trigger which records when each record was last updated.
SELECT empno, ename, update_ts FROM emp WHERE empno=7839; EMPNO ENAME UPDATE_TS ---------- ---------- -------------------- 7839 KING 2001.07.09 23:07:53
Now the trigger is overriding any user supplied values. Even if we supply a value for UPDATE_TS, the trigger will override it.
UPDATE emp
SET sal=sal*.9,
update_ts='1999.07.09 05:32:00'
WHERE empno=7839;
1 row updated.
SELECT empno, ename, update_ts FROM emp WHERE empno=7839;
EMPNO ENAME UPDATE_TS
---------- ---------- --------------------
7839 KING 2001.07.09 23:10:13
Auditing
Oracle has some built in auditing functionality. However, Oracle's auditing records only information at the statement level and cannot access the affected row values. You can supplement Oracle's auditing using row triggers.
CREATE OR REPLACE TRIGGER audit_emp_tr AFTER DELETE OR INSERT OR UPDATE ON EMP FOR EACH ROW DECLARE v_msg VARCHAR2(10); BEGIN IF DELETING THEN v_msg:='DELETE'; ELSIF UPDATING THEN v_msg:='UPDATE'; ELSE --Must be INSERTING v_msg:='INSERT'; END IF; INSERT INTO audit_emp (user_name, action, action_ts, empno, old_sal, new_sal) VALUES (USER, v_msg, TO_CHAR(SYSDATE,'MM/DD HH24:MI'), :old.empno, :old.sal, :new.sal); END; /
Privileges
Triggers execute with the privilege of the owner (the definer). You do not grant rights on triggers, only on the actions which cause the triggers to fire. Therefore if you have the privilege to INSERT into a table, you have the right to fire all INSERT triggers on that table.
Managing triggers
You can list all package specifications you own with the data dictionary view USER_OBJECTS:
SELECT object_name, object_type, created, timestamp, status FROM user_objects WHERE object_type='TRIGGER';OBJECT_NAME OBJECT_TYPE CREATED TIMESTAMP STATUS ------------------------------ ------------------ --------- ------------------- ------- DERIVE_COMM_TR TRIGGER 09-JUL-01 2001-07-09:23:07:53 VALID HI_SAL_TR TRIGGER 09-JUL-01 2001-07-09:23:07:53 VALID TRIGGER_TIMING_AR TRIGGER 09-JUL-01 2001-07-09:23:29:47 VALID TRIGGER_TIMING_AS TRIGGER 09-JUL-01 2001-07-09:23:29:46 VALID TRIGGER_TIMING_BR TRIGGER 09-JUL-01 2001-07-09:23:29:46 VALID TRIGGER_TIMING_BS TRIGGER 09-JUL-01 2001-07-09:23:29:46 VALID UPDATE_EMP_TR TRIGGER 09-JUL-01 2001-07-09:23:02:44 VALID
Curiously, the source code for a trigger is not in USER_SOURCE but instead in USER_TRIGGERS. User triggers contains basically all the information about a specific trigger.
DESC user_triggersName Null? Type ----------------------------------------- -------- -------------- TRIGGER_NAME VARCHAR2(30) TRIGGER_TYPE VARCHAR2(16) TRIGGERING_EVENT VARCHAR2(216) TABLE_OWNER VARCHAR2(30) BASE_OBJECT_TYPE VARCHAR2(16) TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) REFERENCING_NAMES VARCHAR2(128) WHEN_CLAUSE VARCHAR2(4000) STATUS VARCHAR2(8) DESCRIPTION VARCHAR2(4000) ACTION_TYPE VARCHAR2(11) TRIGGER_BODY LONG
To get the source code for a trigger:
SQL> SHOW LONG long 80 SQL> SET LONG 1000 SQL> SELECT trigger_body FROM user_triggers WHERE trigger_name='DERIVE_COMM_TR';TRIGGER_BODY ------------------------------------------------------------ BEGIN --Only salesman get commission IF (:o.comm IS NULL AND :n.comm IS NULL) THEN