The Nuances of Triggers

DML statements, as opposed to row or column events, fire triggers. Rows and columns are the level on which integrity constraints operate, and integrity constraints usually fire before triggers on actual manipulation of the data. This means you have a general rule of thumb to follow, as alluded to in the preceding chapter, that when you need a broader scope of integrity-checking, triggers are the way to go. At a more granular level, the built-in integrity constraints are what you use. The two constructs have very clearly defined “fields of coverage,” so to speak. Usually was put in italics because there is a new trigger that fires before constraints, even before the DRI constraints.

The T-SQL statements listed here are not allowed in trigger code:

• ALTER DATABASE

• CREATE DATABASE

• DROP DATABASE

• LOAD DATABASE

• RESTORE DATABASE

• DISK INIT

• DISK RESIZE

• RECONFIGURE

• LOAD LOG

• RESTORE LOG

While you can use SELECT statements in a trigger and generate result set returns, the client connections that fire the trigger usually have no means of interpreting or working with the returned data. You should accordingly avoid returning results by avoiding open SELECT statements in trigger code. By “open” I mean that the result set is not assigned to an internal structure. In addition, you should use SET NOCOUNT at the beginning of the trigger to obviate the return of any data to the client connection. You should also refrain from using cursors in triggers, because overuse can be a drain on server resources. In any event, you should be working with rowset functionality if you need to work with multiple rows in trigger code (see the discussion of cursors in Chapter 16).

Also, keep in mind that during a TRUNCATE TABLE operation (which is a delete en masse that empties a table by deallocating the table’s data pages), trigger firing is suppressed. This applies to the database owner and should not be a concern of users. And the WRITETEXT statement does not activate a trigger.