database triggers

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
[]
[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