Triggers

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: 

CREATE TRIGGER OrderRemoved ON Orders
AFTER DELETE
AS
EXEC xp_sendmail 'president@whitehouse.gov', 'A record has been deleted from the Orders table' 

If you later get an angry phone call from the President asking you to stop sending him e-mail and decide to redirect your messages to the Vice President, you could modify the trigger using the ALTER TRIGGER command, which has the same basic syntax: 

ALTER TRIGGER OrderRemoved ON Orders
AFTER DELETE
AS
EXEC xp_sendmail 'vicepresident@whitehouse.gov', 'A record has been deleted from the Orders table' 

If you forget the name of your trigger, you may obtain a list of all of the triggers that fire based upon the Orders table by using the command: 

sp_helptrigger Orders 

If you'd like to view the SQL behind a specific trigger, you may view it by issuing the command:

sp_helptext OrderRemoved 

Finally, if you later decide that you want to completely remove a trigger, the syntax is: 

DROP TRIGGER OrderRemoved 

Triggers add a tremendously powerful capability to database servers. The examples mentioned above are relatively trivial, but you should keep in mind that triggers are indeed stored procedures and you may use them to perform any action that a stored procedure can perform. This includes calling other stored procedures, accessing information from other data sources and modifying the contents of other database tables.