Triggers

There are several Oracle database triggers available – triggers are compiled PL/SQL code that fire automatically on the database following a DML action on a table. Triggers available are :

BEFORE INSERT – Fires before an insert on a table.
AFTER INSERT – Fires after an insert on a table.
BEFORE UPDATE – Fires before an update on a table.
AFTER UPDATE – Fires after an update on a table.
BEFORE DELETE – Fires before a delete on a table.
AFTER DELETE – Fires after a delete on a table.

Syntax

CREATE [ OR REPLACE ] TRIGGER BEFORE INSERT | AFTER INSERT |
BEFORE UPDATE | AFTER UPDATE |
BEFORE DELETE | AFTER DELETE
ON [ FOR EACH ROW ]
DECLARE
[ declaration section ]
BEGIN
Execution section
[ EXCEPTION ]
Exception section
END ;

Example

The following code is a BEFORE INSERT trigger that will set a couple of admin columns on a table of CREATED USER & CREATED DATE.
CREATE OR REPLACE TRIGGER trg_before_ins_emp
BEFORE INSERT ON employee FOR EACH ROW
DECLARE
v_username VARCHAR2(30);
BEGIN
SELECT user INTO v_username
FROM dual;
:NEW.created_user := v_username;
:NEW.created_date := SYSDATE;
END trg_before_ins_emp;

The next example will use the AFTER DELETE trigger to create an audit record for any records deleted from the database.
CREATE OR REPLACE TRIGGER trg_after_del_emp
AFTER DELETE ON employee FOR EACH ROW
DECLARE
v_username VARCHAR2(30);
BEGIN
SELECT user INTO v_username
FROM dual;
INSERT INTO employee_audit
( employee_id,
First_name,
Surname,
Date_of_birth,
Manager_ind,
Deleted_by,
Deleted_date )
( :OLD.employee_id,
:OLD.first_name,
:OLD.surname,
:OLD.date_of_birth,
:OLD.manager_ind,
v_username,
SYSDATE );
END trg_after_del_emp;

Once compiled on the database a trigger can be DROPPED, ENABLED or DISABLED. The ability to enable and disable triggers is useful to avoid having to drop them and re-create. The syntax is as follow :
DROP TRIGGER ;
ALTER TRIGGER DISABLE;
ALTER TRIGGER ENABLE;

Leave a Reply

Your email address will not be published. Required fields are marked *