Triggers offer database developers and administrators a tremendous degree of flexibility. They are, quite simply, stored procedures that may be configured to automatically execute (or "fire" in tech lingo) when certain events take place.

All enterprise-level relational databases support triggers in one form or another. We'll take a brief look at the syntax used by Microsoft SQL Server, but the basic concepts in this article apply equally to Oracle databases. There are only some slight differences in keywords and formatting.

The basic command used to create a new trigger is the CREATE TRIGGER statement. This statement is followed by the details of when the trigger should fire. Triggers may be associated with INSERT, UPDATE and DELETE events and may be used either INSTEAD OF or AFTER the specified operation. Once you've specified the trigger criteria, you supply the SQL statement that contains the trigger's payload. For example, if we wanted to configure a simple trigger to send an e-mail to the president each time a record is removed from the Orders table, we would use the following syntax:


Trigger Creation

The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert, update, or delete operation. If an action query has an AFTER trigger, the trigger fires after the successful completion of action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. In other words the action query is never executed.

Read Users' Comments (0)

0 Response to "Triggers"

Post a Comment