Summary

Triggers are stored procedures you never execute. The system executes them in response to a data modification statement on a table. Triggers are only executed once per statement, no matter how many rows are modified by the statement (0, 1, or many).

SQL Server uses transactions and locks to guarantee that triggers run in isolation. Your trigger can use the inserted and deleted tables to access before and after images of the data that was modified.

Trigger code needs to be lean and well-tested. Be sure to watch out for the multirow case, and to check for errors and other inconsistencies.

Q&A

Q Can I create a trigger on a system table?

A You can create a trigger on certain system tables and they will fire. (During my casual testing, a trigger on sysusers fired after I executed sp_adduser; a trigger on sysobjects did not fire after CREATE TABLE. You can easily test the behavior for any operations on a table you would like to monitor. Be sure to conduct your tests in a database you aren’t concerned about losing!)

There are some interesting applications for triggers on system tables. You can send automated e-mail notifications to the dbo or sa if there are changes to important tables. You can audit modifications to those tables.

If you are considering a trigger on a system table, you might find it easier to modify the appropriate system procedures instead. Most modifications to system tables occur in those procedures; it will certainly be easier to maintain a single unit of code (a procedure) than two (a procedure and a trigger).

One word of caution: don’t try to put a trigger on syslogs (or make any direct modifications to syslogs). Direct updates to syslogs can send the server into an infinite loop!

Q Are there actions you can’t perform in a trigger?

A Some of the restrictions from earlier versions of SQL Server have been relaxed. In general, you can’t do any work that alters the transaction logs or the structure of tables or other objects. Here’s a brief list of what’s against the rules:

•  Creating objects or databases

•  Dropping objects or databases

•  Altering tables or databases

•  Truncating tables (this operation is minimally logged, so it could imperil the integrity of the trigger and its transaction context)

•  Granting and revoking privileges

•  Updating statistics

•  Reconfiguring the server

•  Dumping databases and transactions (backing up data)

•  Loading databases and transactions (restoring from backup)

•  Generating disk statements (to create and manage devices)

With release 6.5, it’s now possible to use SELECT…INTO to create a temporary or permanent table on the fly. (As it turns out, creating a permanent table on the fly won’t be very effective because concurrent users will collide with each other on the object name. If you think of a good application for creating a permanent table in a trigger, please let us know!)

On the other hand, creating a temporary table with SELECT…INTO has many applications in a trigger. Most importantly, you can use a temporary table as a space to pass the contents of the inserted or deleted table to a stored procedure. Without this capability, you need to use a permanent worktable to pass those values (remember, procedure parameters are scalar values, not arrays), and that introduces complex multiuser performance and concurrency issues.

Q Which is faster, a stored procedure or a trigger?

A Stored procedures and triggers use the same execution method to run, including the use of procedure cache to store precompiled execution plans. So, technically speaking, neither is faster.

On the other hand, you can often design a better performing system by using stored procedures than by relying on triggers. The difference is in the order of execution. Triggers fire only after the data is in the table. If any validation tests fail in a trigger, the work already performed needs to be rolled back.

Stored procedures execute in the order you designate. You can check data validity first, so data modifications are only performed on good data. This can reduce concurrency problems and improve overall system performance.

Q Can I use a trigger to assign a unique row ID?

A Yes, but it’s probably not a good idea for a number of reasons. The most efficient method of assigning unique counter values is to create an identity column for the table, coupled with a unique index. Some people are reluctant to use identity columns because identity columns are frequently “burned,” that is, used up without being assigned to an actual row.

Here’s what the code would look like to manage counters in a trigger:

/*  please  don’t  do  this  */
create   trigger     tr
on   t
for    insert
as
if @@rowcount  =  0  return
declare  c  cursor  for
select    altid
from    inserted
open  c
declare  @altid  int
fetch  c  into  @altid
while  @@fetch_status  =  0
begin
   update  counter_table
      set @id = id = id + 1
   update t
      set id = @id
    where altid = @altid
   fetch c into @altid
end
close   c
deallocate   c
return

Several performance problems are introduced. First, the trigger needs to use a cursor to handle multirow inserts. Cursors are slow, so updates will be slower and locking will be more complex. Second, the use of the counter_table introduces another lock, and this will be a far more intrusive lock than the one on your primary table. Each user performing an insert will need to visit this table and will do so as a part of his or her transaction. You will single-thread all inserts using this method. Contrast this with a memory semaphore used to handle identity counters, where the locking of an identity value is not part of the overall transaction locking function.

This method also requires the use of an alternate key, some other method of uniquely identifying the row you want to update. Without an alternate identifier, it’s impossible to correlate a row in the inserted table with the row in the base table that will obtain the new counter value.

Finally, the additional update on the base table will triple the logging and memory-based data management work versus the comparable trigger approach.