Triggers Concepts - 2

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

DECLARE
act VARCHAR2(1);
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 = 'Cline';

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
FOR ON
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');