Triggers

TRIGGER

An object that fires functionality when the event occurs is trigger. The event which makes this trigger workable are update, delete, etc.

It define actions to be executed automatically when certain events occur and corresponding conditions are satisfied.

Definition: A trigger is a database object that fires a piece of sql code that accomplishes / completes task(s) depending upon event(s) taking place.

TYPEs of TRIGGERS:

Row Trigger: It executes once for each no. in a transaction.

If the triggering statement affects no rows, then that trigger is not executed at all.

Statement Trigger: It executes only once for each transaction.

It is “by default”

Before Trigger: Executes trigger action before the trigger statement. Used when trigger action should determines whether or not the triggering statement should be allowed to complete. By using ‘before’ trigger user can eliminate unnecessary processing of the triggering statement.

After Trigger: Executes the trigger action after triggering statement is executed. Used when the triggering statement before executing the trigger action. No such action.

Uses of Triggers:

• implementing business rules
• audit logging
• carrying out actions outside the db system

Note: triggers are occasionally called rules or active rules.

Triggers have three parts:

• EVENT/ STATEMENT – cause trigger to be fired
• RESTRICTION – Boolean expression must be true for trigger to be fired
• ACTION – executes when triggering statement is encountered.

How to create a Trigger?

CREATE TRIGGER name_of_trigger ON table_name [AFTER/ BEFORE] INSERT/ UPDATE/ DELETE/ SELECT FOR EACH ROW

BEGIN

{

        --query/ statements--

}

END;

//to delete

DROP TRIGGER trigger_name;