• A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.

• Triggers are procedures that are stored in the database and are implicitly run, or fired, when something happens.

• To design a trigger mechanism we must:

• Specify the conditions under which the trigger is to be executed.

• Specify the actions to be taken when the trigger executes.

Generalized Syntax:

Create Trigger < trigger name>

After/Before <insert OR delete OR update>

OF <columnname> ON <tablename>

FOR EACH ROW

WHEN <condition>

<trigger actions>

• Here triggering event can be insert, delete or update.

• Triggers on update can be restricted to a specific attributes.

• Values of attributes before and after an update can be referenced.

Consider two relations as:

emp(id, Name, sal, dno)

Dept(dno, dname, totalsal, managerid)

Consider trigger as:

Create Trigger $T_2$

After Insert on emp

For each row

When (New, dno IS NOT NULL)

Update Dept

Set total sal + = New.sal

Where dno = NEW.dno

• This trigger wil be activated when a new employee entry is done in emp table.

• The salary of new employee gets added to the total salary of department and that value is updated in department (dept) table.

• Deletion of trigger can be done using:

DROP TRIGGER < trigger name>

• Triggers are useful in many cases when it is convenient to specify action to be taken when certain event occurs and certain conditions are satisfied.