General |
Data Dictionary Views Related To DDL Triggers | trigger$
dba_triggers | all_triggers | user_triggers | |
System Privileges Related To Table Triggers | create trigger create any trigger administer database trigger alter any trigger drop any trigger |
Table Trigger Firing Options | -- before constraints are applied BEFORE INSERT BEFORE UPDATE BEFORE DELETE -- after constraints are applied AFTER INSERT AFTER UPDATE AFTER DELETE |
Transaction Model | Oracle transactions are atomic. No commit or rollback are allowed in a trigger. |
Maximum trigger size | 32K - but you can call procedures and function in triggers to perform processing |
|
Create Statement Level Triggers (the default) |
Statement Level Trigger With A Single Action | CREATE OR REPLACE TRIGGER <trigger_name> [<ENABLE | DISABLE>] <BEFORE | AFTER> <ACTION> [OR <ACTION> OR <ACTION>] ON <table_name> [FOLLOWS <schema.trigger_name>] DECLARE <variable definitions> BEGIN <trigger_code> EXCEPTION <exception clauses> END <trigger_name>; / |
CREATE TABLE orders ( somecolumn VARCHAR2(20), numbercol NUMBER(10), datecol DATE); CREATE OR REPLACE TRIGGER statement_level BEFORE UPDATE ON orders DECLARE vMsg VARCHAR2(30) := 'Statement Level Trigger Fired'; BEGIN dbms_output.put_line(vMsg); END statement_level; / set serveroutput on INSERT INTO orders (somecolumn) VALUES ('ABC'); UPDATE orders SET somecolumn = 'XYZ'; |
Statement Level Trigger With Multiple Actions |
CREATE OR REPLACE TRIGGER statement_level AFTER INSERT OR UPDATE OR DELETE ON orders DECLARE vMsg VARCHAR2(30) := 'Statement Level Trigger Fired'; BEGIN IF INSERTING THEN dbms_output.put_line(vMsg || ' When Inserting'); ELSIF UPDATING THEN dbms_output.put_line(vMsg || ' When Updating'); ELSIF DELETING THEN dbms_output.put_line(vMsg || ' When Deleting'); END IF; END statement_level; / set serveroutput on INSERT INTO orders (somecolumn) VALUES ('ABC'); UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1; DELETE FROM orders WHERE ROWNUM = 1; |
|
Create Row Level Triggers |
Note: AFTER row triggers create less UNDO than BEFORE row triggers so use AFTER when possible. |
Row Level Trigger ... most common usage to provide a surrogate key from a sequence | CREATE OR REPLACE TRIGGER <trigger_name> [FOLLOWS <schema.trigger_name>] [<ENABLE | DISABLE>] <BEFORE | AFTER> <ACTION> OR <ACTION> OR <ACTION> [OF <column_name_list>] ON <table_name> REFERENCING NEW AS <synonym> OLD AS <synonym> PARENT AS <synonym> FOR EACH ROW DECLARE <variable definitions> BEGIN <trigger_code> EXCEPTION <exception clauses> END <trigger_name>; / |
CREATE TABLE t ( rid NUMBER(5), col VARCHAR2(3)); ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (rid) USING INDEX; CREATE SEQUENCE seq_t; CREATE OR REPLACE TRIGGER row_level BEFORE INSERT ON t FOR EACH ROW BEGIN SELECT seq_t.NEXTVAL INTO :NEW.rid FROM DUAL; dbms_output.put_line(:NEW.rid); END row_level; / INSERT INTO t (col) VALUES ('A'); INSERT INTO t (col) VALUES ('B'); INSERT INTO t (col) VALUES ('C'); SELECT * FROM t; |
Row Level Trigger With A Single Action |
CREATE OR REPLACE TRIGGER row_level BEFORE UPDATE ON orders FOR EACH ROW DECLARE vMsg VARCHAR2(30) := 'Row Level Trigger Fired'; BEGIN dbms_output.put_line(vMsg); END row_level; / set serveroutput on INSERT INTO orders (somecolumn) VALUES ('ABC'); INSERT INTO orders (somecolumn) VALUES ('ABC'); INSERT INTO orders (somecolumn) VALUES ('ABC'); SELECT * FROM orders; UPDATE orders SET somecolumn = 'XYZ'; |
Row Level Trigger With Multiple Actions |
CREATE OR REPLACE TRIGGER statement_level AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW DECLARE vMsg VARCHAR2(30) := 'Row Level Trigger Fired'; BEGIN IF INSERTING THEN dbms_output.put_line(vMsg || ' On Insert'); ELSIF UPDATING THEN dbms_output.put_line(vMsg || ' On Update'); ELSIF DELETING THEN dbms_output.put_line(vMsg || ' On Delete'); END IF; END statement_level; / set serveroutput on INSERT INTO orders (somecolumn) VALUES ('ABC'); UPDATE orders SET somecolumn = 'ZZT'; DELETE FROM orders WHERE rownum < 4; |
Row Level Trigger With OF Clause |
CREATE OR REPLACE TRIGGER of_clause BEFORE UPDATE OF numbercol ON orders FOR EACH ROW DECLARE vMsg VARCHAR2(40) := 'Update Will Change numbercol Column'; BEGIN dbms_output.put_line(vMsg); END of_clause; / set serveroutput on UPDATE orders SET numbercol = 8; |
Row Level Trigger With Referencing Clause |
CREATE TABLE person ( fname VARCHAR2(15), lname VARCHAR2(15)); CREATE TABLE audit_log ( o_fname VARCHAR2(15), o_lname VARCHAR2(15), n_fname VARCHAR2(15), n_lname VARCHAR2(15), chng_by VARCHAR2(10), chng_when DATE); CREATE OR REPLACE TRIGGER referencing_clause AFTER UPDATE ON person REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO audit_log (o_fname, o_lname, n_fname, n_lname, chng_by, chng_when) VALUES (:OLD.fname, :OLD.lname, :NEW.fname, :NEW.lname, USER, SYSDATE); END referencing_clause; / INSERT INTO person (fname, lname) VALUES ('Dan', 'Morgan'); SELECT * FROM person; SELECT * FROM audit_log; UPDATE person SET lname = 'Dangerous'; SELECT * FROM person; SELECT * FROM audit_log; UPDATE person SET fname = 'Mark', lname = 'Townsend'; SELECT * FROM person; SELECT * FROM audit_log; |
Follows Clause | CREATE TABLE test ( testcol VARCHAR2(15)); INSERT INTO test VALUES ('dummy'); CREATE OR REPLACE TRIGGER follows_a AFTER UPDATE ON test FOR EACH ROW BEGIN dbms_output.put_line('A'); END follows_a; / CREATE OR REPLACE TRIGGER follows_b AFTER UPDATE ON test FOR EACH ROW BEGIN dbms_output.put_line('B'); END follows_b; / set serveroutput on UPDATE test SET testcol = 'a'; CREATE OR REPLACE TRIGGER follows_b AFTER UPDATE ON test FOR EACH ROW FOLLOWS uwclass.follows_a BEGIN dbms_output.put_line('B'); END follows_b; / UPDATE test SET testcol = 'a'; |
|
Compound Triggers (new 11g) |
Compound triggers allow for writing a single trigger incorporating STATEMENT and ROW LEVEL and BEFORE and AFTER | CREATE TRIGGER <trigger_name> FOR <triggering_event> ON <table_name> COMPOUND TRIGGER BEFORE STATEMENT IS BEGIN ... END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN ... END BEFORE EACH ROW; AFTER STATEMENT IS BEGIN ... END AFTER STATEMENT; AFTER EACH ROW IS BEGIN ... END AFTER EACH ROW; END compound_trigger; / |
CREATE TABLE test AS SELECT table_name, tablespace_name FROM user_tables; set serveroutput on CREATE OR REPLACE TRIGGER compound_trig FOR INSERT ON test COMPOUND TRIGGER ------------------------------- BEFORE STATEMENT IS BEGIN dbms_output.put_line('BEFORE STATEMENT LEVEL'); END BEFORE STATEMENT; ------------------------------- BEFORE EACH ROW IS BEGIN dbms_output.put_line('BEFORE ROW LEVEL'); END BEFORE EACH ROW; ------------------------------- AFTER STATEMENT IS BEGIN dbms_output.put_line('AFTER STATEMENT LEVEL'); END AFTER STATEMENT; ------------------------------- AFTER EACH ROW IS BEGIN dbms_output.put_line('AFTER ROW LEVEL'); END AFTER EACH ROW; END compound_trig; / SELECT trigger_name, trigger_type FROM user_triggers; INSERT INTO test (table_name, tablespace_name) VALUES ('MORGAN', 'UWDATA'); |
|
Altering Triggers (all types) |
Disable A Single Trigger | ALTER TRIGGER <trigger_name> DISABLE; |
CREATE TABLE t ( testcol VARCHAR2(20)); CREATE OR REPLACE TRIGGER bi_t BEFORE INSERT ON t BEGIN NULL; END bi_t; / CREATE OR REPLACE TRIGGER bd_t BEFORE DELETE ON t BEGIN NULL; END bd_t; / SELECT trigger_name, status FROM user_triggers; ALTER TRIGGER bi_t DISABLE; SELECT trigger_name, status FROM user_triggers; ALTER TABLE t DISABLE ALL TRIGGERS; SELECT trigger_name, status FROM user_triggers; ALTER TRIGGER bd_t ENABLE; SELECT trigger_name, status FROM user_triggers; ALTER TABLE t ENABLE ALL TRIGGERS; SELECT trigger_name, status FROM user_triggers; |
Disable All Triggers On A Table | ALTER TABLE <table_name> DISABLE ALL TRIGGERS; |
See DISABLE Demo; |
Enable A Single Trigger | ALTER TRIGGER <trigger_name> ENABLE; |
See DISABLE Demo; |
Enable All Triggers On A Table | ALTER TABLE <table_name> ENABLE ALL TRIGGERS; |
See DISABLE Demo; |
Rename Trigger | ALTER TRIGGER <trigger_name> RENAME TO <new_name>; |
ALTER TRIGGER bi_t RENAME TO new_trigger_name; SELECT trigger_name, status FROM user_triggers; |
|
Drop Trigger (all types) |
Drop Trigger | DROP TRIGGER <trigger_name>; |
DROP TRIGGER new_trigger_name; |
|
Trigger With Autonomous Transaction |
The same simple trigger as an autonomous transaction | ALTER TABLE audit_log ADD (commentcol VARCHAR2(50)); desc audit_log CREATE OR REPLACE TRIGGER t_autonomous_tx BEFORE INSERT ON person DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO audit_log (chng_when, commentcol) VALUES (SYSDATE, 'Reporting an error'); COMMIT; END t_autonomous_tx; / INSERT INTO person (fname) VALUES ('abcdefghijklmnopqrst'); SELECT * FROM person; SELECT chng_when, commentcol FROM audit_log; |
|
Cascading Triggers |
Cascading Trigger Demo | CREATE TABLE cascade ( testcol VARCHAR2(10)); CREATE OR REPLACE TRIGGER t_cascade AFTER INSERT ON cascade BEGIN INSERT INTO cascade (testcol) VALUES ('change'); END t_cascade; / INSERT INTO cascade (testcol) VALUES ('ABC'); |
|
Mutating Triggers |
Mutating Trigger Demo The insert into t1 firest the trigger which attempts to count the number of records in t1 ... which is ambiguous. | CREATE TABLE t1 (x int); CREATE TABLE t2 (x int); INSERT INTO t1 VALUES (1); SELECT * FROM t1; SELECT * FROM t2; CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE i PLS_INTEGER; BEGIN SELECT COUNT(*) INTO i FROM t1; INSERT INTO t2 VALUES (i); END; / INSERT INTO t1 VALUES (1); SELECT COUNT(*) FROM t1; SELECT COUNT(*) FROM t2; |
Mutating Trigger Fix With Autonomous Transaction Count on t1 is performed as though a different user logged on and asked the question of t1. | CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; i PLS_INTEGER; BEGIN SELECT COUNT(*) INTO i FROM t1; INSERT INTO t2 VALUES (i); COMMIT; END; / INSERT INTO t1 VALUES (1); SELECT COUNT(*) FROM t1; SELECT COUNT(*) FROM t2; |
|
Trigger Enforcing Auditing |
Auditing With A Statement Trigger | CREATE TABLE changes ( fnew VARCHAR2(10), fold VARCHAR2(10), changed_by VARCHAR2(30) DEFAULT USER); CREATE OR REPLACE TRIGGER t_t2_audit AFTER UPDATE ON t2 REFERENCING NEW AS n OLD AS o FOR EACH ROW BEGIN INSERT INTO changes VALUES (:n.x, :o.x, user); END t_t2_audit; / SELECT * FROM t2; UPDATE t2 SET x = 1; COMMIT; SELECT * FROM changes; |
|
Trigger Enforcing Integrity Constraint |
Trigger To Disallow Entry Of Future Dates | CREATE OR REPLACE TRIGGER t_date BEFORE INSERT ON orders FOR EACH ROW DECLARE bad_date EXCEPTION; BEGIN IF :new.datecol > SYSDATE THEN RAISE_APPLICATION_ERROR(-20005,'Future Dates Not Allowed'); END IF; END; / INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1); INSERT INTO orders VALUES ('ABC', 999, SYSDATE); INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1); |
|
Trigger Ensuring Data Entry During Business Hours |
Trigger To Disallow Entry Of Future Dates | CREATE OR REPLACE PROCEDURE secure_dml(dateval IN DATE)IS BEGIN IF TO_CHAR (dateval, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00' OR TO_CHAR (dateval, 'DY') IN ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR (-20205, 'Changes only allowed during office hours'); END IF; END secure_dml; / CREATE OR REPLACE TRIGGER secure_data BEFORE INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW BEGIN secure_dml(:NEW.datecol); END; / INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1); INSERT INTO orders VALUES ('ABC', 999, SYSDATE-4/24); INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1); |