Triggers

Triggers are blocks of code that are executed whenever triggering events happen. Triggering events can be DML statements (INSERT, UPDATE, DELETE) on a particular table or view issued by any user, DDL statements (CREATE or ALTER primarily, DROP also) or some database events like logon/logoff, errors or startup/shutdown. The timing can be either BEFORE, AFTER or INSTEAD OF.

The trigger can be fired either per row or per statement.

Syntax for trigger on a DML event:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE|AFTER|INSTEAD OF|FOR } trigger_event
ON {table_or_view_reference |
NESTED TABLE nested_table_column OF view}
[REFERENCING [OLD AS old] [NEW AS new]
[PARENT AS parent]]
[FOR EACH ROW ] — defines the trigger to be a row level trigger
[FOLLOWS other_trigger] — firing multiple triggers on the same event, FOLLOWS give the order
[DISABLE] — triggers are enabled by default. They can be disabled by creation with this command
[WHEN trigger_condition] — conditions that must be met for a trigger to fire
trigger_body;

Syntax for trigger on a DDL event or database event:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE|AFTER } trigger_event
ON [DATABASE|schema]
[FOLLOWS other trigger][DISABLE]
[WHEN trigger_condition]
trigger_body;

An INSERT example trigger:

CREATE OR REPLACE TRIGGER add_tstamp
BEFORE INSERT ON emp
REFERENCING NEW as new_row
FOR EACH ROW
FOLLOWS audit_emp
BEGIN
— Automatically timestamp the entry
SELECT CURRENT_TIMESTAMP
INTO :new_row.entry_timestamp
FROM dual;
END add_tstamp;

UPDATE example trigger:

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
— Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;
— Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
END;

CREATE example trigger:

CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA
DECLARE
oper ddl_log.operation%TYPE;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, NULL, USER, SYSDATE
FROM dual;
END bcs_trigger;
/

Triggers are enabled by default on creation and can be disabled with an ALTER statement.

ALTER TRIGGER trigger_name {ENABLE|DISABLE};
ALTER TABLE table_name {ENABLE|DISABLE} ALL TRIGGERS;