CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Table Triggers
Version 11.1
 
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);
 
Related Topics
Autonomous Transaction
DDL Triggers
Exception Handling
Instead Of Trigger
SYS_CONTEXT Function
System Triggers
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [148 users online]    © 2010 psoug.org