Database Reference
In-Depth Information
7.4 Triggers
A special kind of stored procedure is called a
trigger
. When invoked by DML and DDL com-
mands, triggers are executed automatically. he way of executing a trigger is diferent from that of
executing a stored procedure. A trigger can only be ired (executed) when valid data modiication
events such as INSERT, UPDATE, or DELETE occur, or valid data deinition events such as
CREATE, ALTER, or DROP occur. Triggers cannot be ired manually.
Triggers are widely used in database maintenance and in implementing database application
logic. For example, if the enrollment of a class is small, by the university regulation, the class
should be canceled. In this case, you need to remove the class information from the table CLASS
and from other related tables in the database Class_Registration. To accomplish this task, you can
create a trigger to automatically delete the class from tables such as CLASS, STUDENT_CLASS,
and FACULTY_CLASS. Here, triggers play an important role in keeping database integrity. he
following are tasks that can be accomplished by triggers:
◾
When running a database application, implement the application logic dynamically.
◾
When a change is made in a table, modify related values in other associated tables.
◾
When an error occurs during a transaction, roll back the transaction.
◾
When table content is modiied by a DML command, display warning messages.
◾
Before table content is modiied, verify if a new data value complies with the integrity con-
straint in associated tables.
◾
During the execution of a function or a procedure, invoke external programs.
◾
Modify the content of a view that is constructed on multiple tables.
◾
Carry out complex data constraints that may be diicult to implement at the database design
stage.
◾
Respond to events caused by DDL commands such as creating, altering, or dropping a
database object.
Triggers are created on a table or view. When creating triggers, you need to keep the following
rules in mind:
◾
A trigger created on one table cannot be recreated on another table with the same name.
◾
Triggers cannot be created on system tables or temporary tables.
◾
Triggers do not accept input parameters.
◾
Too many triggers created on a single table may slow down database performance.
◾
A trigger can be ired by either DML or DDL commands.
Triggers can be categorized based on the time they are ired and the type of command they
respond to. A trigger can be ired either before or after a change is made to a table or view. If a
trigger is ired before a change is made, it is called an
INSTEAD OF
trigger. Instead of perform-
ing an activity speciied by an SQL statement, the INSTEAD OF trigger performs other activities
speciied by preprogrammed SQL statements in the trigger. For example, a foreign key constraint
requires that values in the foreign key must have a corresponding value in the parent key. To delete
a value in the parent key, instead of simply deleting the value that may cause an integrity constraint
violation, the INSTEAD OF trigger can be used to irst delete all the rows with the corresponding
foreign key values in the child table. For each table or view, you can only create one INSTEAD OF
trigger.
