| 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);
|
