Triggers concepts

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
[]
[OR OR ]
ON
[FOLLOWS ]

DECLARE

BEGIN

EXCEPTION

END ;
/
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
[FOLLOWS ]
[]
OR OR
[OF ]
ON
REFERENCING NEW AS OLD AS PARENT AS
FOR EACH ROW

DECLARE

BEGIN

EXCEPTION

END ;
/
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;