Oracle Table Triggers
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Data Dictionary Objects
ALL_TRIGGERS CDB_TRIGGER_ORDERING TRIGGER$
ALL_TRIGGER_COLS DBA_TRIGGERS USER_TRIGGERS
ALL_TRIGGER_ORDERING DBA_TRIGGER_COLS USER_TRIGGER_COLS
CDB_TRIGGERS DBA_TRIGGER_ORDERING USER_TRIGGER_ORDERING
CDB_TRIGGER_COLS    
System Privileges
ADMINISTER DATABASE TRIGGER CREATE ANY TRIGGER DROP ANY TRIGGER
ALTER ANY TRIGGER CREATE TRIGGER  
Trigger Firing Options
Before Constraints Are Applied   After Constraints Are Applied
BEFORE DELETE   AFTER DELETE
BEFORE INSERT   AFTER INSERT
BEFORE UPDATE   AFTER UPDATE
Maximum trigger size 32K - but you can call procedures and function in triggers to perform processing
Transaction Model Oracle transactions are atomic. No commit or rollback are allowed in a trigger.
 
Create Statement Level Triggers (the default)
Statement Level Trigger With A Single Action CREATE OR REPLACE [<EDITIONABLE | NONEDITIONABLE>]
TRIGGER <trigger_name>
[SHARING = <METADATA | NONE>]
[DEFAULT COLLATION <USING_NLS_COMP>]
<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 the distinction will not affect business rules
Row Level Trigger ... most common usage to provide a surrogate key from a sequence CREATE OR REPLACE [<EDITIONABLE | NONEDITIONABLE>]
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_all_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
Trigger declared as an autonomous transaction conn uwclass/uwclass@pdbdev

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 conn uwclass/uwclass@pdbdev

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		 conn uwclass/uwclass@pdbdev

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

But keep in mind, even though this workaround exists, it is more likely you are not approaching the problem from the correct perspective ... consider options to avoid this.		 conn uwclass/uwclass@pdbdev

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 conn uwclass/uwclass@pdbdev

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 conn uwclass/uwclass@pdbdev

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" conn uwclass/uwclass@pdbdev

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 conn uwclass/uwclass@pdbdev

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);
 
Native Dynamic SQL Demo
Native Dynamic SQL used to disable, and re-enable, all triggers in a schema conn uwclass/uwclass@pdbdev

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;

Related Topics
Built-in Functions
Built-in Packages
Accessible By Clause
Autonomous Transaction
CrossEdition Triggers
DDL Event Triggers
Exception Handling
Instead Of Triggers
SYS_CONTEXT Function
System Event Triggers
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx