PL/SQL - Triggers

Triggers are stored program , which are automatically executed or fired when some events occur.

Triggers are infact executed in response of the following events:

• DML operation - delete, insert and update
• DDL operation - create, alter or drop
• Other db operations - servererror, logon, logoff, shutdown

Triggers could be defined on table, view, schema or db with which the event is associated.

Benefits of Triggers:

• Generating some derived column values automatically
• Enforcing referencial integrity
• Event logging and storing info on table access
• Auditing
• Imposing security authorization
• Preventing invalid transactions

Trigger types:

• Row level Triggers
• Statement level triggers
• Before triggers
• After triggers

Row level Triggers:

Row level triggers execute once for each row in a transaction. For example, if an update statement updates multiple rows of a table, a row level trigger is fired once for each row affected by the update statement.

Example, an update statement updates 3 rows in a table, then 3 triggers will be fired.

Statement level Triggers:

Statement level triggers are triggered only once for each transaction. For example, when an update commands update 2 rows, the commands contained in the trigger are executed only once.

Before Triggers:

Before trigger execute the trigger action before the triggering event(insert, update or delete). 

• Before triggers are used to drive specific column values before completing a triggering insert, update operation.
• Before triggers are used when the trigger action should determine whether or not the triggering statement should be allowed to complete.

After Triggers:

After trigger executes the trigger action after the triggering event(insert, update or delete).

Example, 

To perform cascade delete operation, it means that user delete the record from one table but the corresponding records in other tables are deleted automatically by a trigger which fired after the execution of delete statement issued by the user.

Example 1 - statment level trigger:

When we will update the customet table, before the update, the following trigger will be fired and put an audit entry into the audit table.

CREATE TRIGGER customer_before_update

BEFORE UPDATE

ON CUSTOMER

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'BEFORE UPDATE OPERATION');

END;

Example 2 - staement level with more events

CREATE TRIGGER customer_after_action

AFTER INSERT OR DELETE OR UPDATE

ON CUSTOMER

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

IF INSERTING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION');

ELSIF DELETING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'DELETE OPERATION');

ELSIF UPDATING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'UPDATE OPERATION');

END IF;

END;

Example row level trigger:

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION ROW LEVEL');

END;

OLD and NEW pseudo records with an example:

When a row level trigger fires, the PL/SQL runtime system creates and populates the two pseudo records OLD and NEW.

• For an Insert trigger, OLD : none, NEW: nee value
• For an Update trigger, OLD: old value, NEW: New value
• For a Delete trigger, OLD: old value, NEW: none

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Row level Triggers Example with WHEN clause:

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

WHEN (OLD.REGION = 'SOUTH')

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Row level triggers with OF clause

CREATE TRIGGER customer_after_update

AFTER UPDATE

OF CUSTOMER_ID

ON CUSTOMER

FOR EACH ROW

WHEN (OLD.REGION = 'SOUTH')

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Disable and enable triggers:

Disable/Enable:

ALTER TRIGGER <TRIGGERNAME> DISABLE;

ALTER TRIGGER <TRIGGERNAME> ENABLE;

Disable / enable all the triggers present in the table:

ALTER TABLE <TABLE NAME> DISABLE ALL TRIGGERS;

ALTER TABLE <TABLE NAME> ENABLE ALL TRIGGERS;

Drop a trigger:

DROP  TRIGGER <TRIGGER NAME>;

Rename a trigger:

ALTER TRIGGER <OLD TRIGGER NAME> RENAME TO <NEW TRIGGER NAME>