Determining When to Use Triggers

When you are creating a SQL database, you must choose whether you want to write all your table modification scripts using standard SQL, or put many of the modification routines into stored procedures. If you use stored procedures to handle these modifications and you revoke any direct permission to insert, update, and delete rows from the tables, you probably don't need to use triggers. However, if you choose not to use stored procedures, or you cannot use them due to restrictions in the database connection software you are using, triggers are the only way to get things done efficiently.

Triggers become part of the table by attaching themselves to one or more of the standard actions against that table. After you have created a trigger for an action on a table, the trigger will fire every time that action occurs. This doesn't mean that you should use triggers to perform all the validation on the database. There are many other ways to perform many of the data validation tasks. For example, indexes are far quicker to check uniqueness than triggers. What follows is a list of some of the do's and don'ts of using triggers.