| General Information |
| Data Dictionary Views |
| ALL_TRIGGERS |
DBA_TRIGGERS |
TRIGGER$ |
USER_TRIGGERS |
| ALL_TRIGGER_COLS |
DBA_TRIGGER_COLS |
|
USER_TRIGGER_COLS |
| ALL_TRIGGER_ORDERING |
DBA_TRIGGER_ORDERING |
|
USER_TRIGGER_ORDERING |
|
| 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>
<BEFORE | AFTER> <ACTION> [OR <ACTION> OR <ACTION>]
ON <table_name>
[FOLLOWS <schema.trigger_name>]
[<ENABLE | DISABLE>]
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'; |
Best practice when creating new triggers in a production database is to create them in a disabled state.
By doing this there is no chance that an error in the trigger will cause DML on the table to fail.
An excellent tip delivered at UKOUG 2009 by Connor McDonald  |
CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders
DISABLE
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/
SELECT trigger_name, status
FROM user_triggers;
ALTER TRIGGER statement_level ENABLE;
SELECT trigger_name, status
FROM user_triggers; |
| 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('NUMBERCOL') THEN
dbms_output.put_line(vMsg || ' Updating Numbercol');
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;
UPDATE orders SET numbercolumn = 9 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>]
[WHEN (<condition>)]
<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 row_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 row_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 WHEN Clause |
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders SET numbercol = 0, datecol = SYSDATE;
UPDATE orders SET numbercol = 1 WHERE somecolumn = 'ZZT' AND rownum = 1;
SELECT * FROM orders;
CREATE OR REPLACE TRIGGER when_clause
BEFORE UPDATE
ON orders
FOR EACH ROW
WHEN (NEW.numbercol <> OLD.numbercol AND NEW.numbercol < 5)
BEGIN
dbms_output.put_line('Fired with value: ' || :NEW.numbercol);
END when_clause;
/
set serveroutput on
SELECT * FROM orders;
UPDATE orders
SET numbercol = 8;
UPDATE orders
SET numbercol = 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 |
| 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); |
| |
| Fine Grained Dependency Tracking |
| This demonstration should be run in both 11gR2 and prior versions.
In prior versions the trigger is invalidated by adding or dropping the column "newcol" |
CREATE TABLE t1 (
testcol NUMBER(3));
CREATE OR REPLACE TRIGGER bi_t
BEFORE INSERT
ON t1
FOR EACH ROW
BEGIN
:NEW.testcol := 1;
END;
/
SELECT trigger_name, status
FROM user_triggers;
ALTER TABLE t
ADD (newcol VARCHAR2(3));
SELECT trigger_name, status
FROM user_triggers;
ALTER TABLE t DROP COLUMN newcol;
SELECT trigger_name, status
FROM user_triggers; |
| |
| 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); |
| |
| NDS Demo |
| Native Dynamic SQL used to disable, and re-enable, all triggers in a schema |
SELECT status, COUNT(*)
FROM user_triggers
GROUP BY status;
DECLARE
exstr VARCHAR2(100);
CURSOR dtt_cur IS
SELECT DISTINCT table_name
FROM user_triggers;
BEGIN
FOR dtt_rec IN dtt_cur LOOP
exstr := 'ALTER TABLE ' || dtt_rec.table_name || ' DISABLE ALL TRIGGERS';
execute immediate exstr;
END LOOP;
END;
/
SELECT status, COUNT(*)
FROM user_triggers
GROUP BY status; |
