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.
Create Trigger < trigger name>
After/Before <insert OR delete OR update>
OF <columnname> ON <tablename>
FOR EACH ROW
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)
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.