I coded a few triggers. Here's my 2 bits.

In the trigger you have access to the "inserted" and "deleted" tables. They will let you do set based queries using all the inserted, updated, or deleted records.

For update triggers, having a key that is not updated (or an identity field that's not updated) will let you compare the before and after of each record. Otherwise, it can be difficult to know which "inserted" record corresponds to which "deleted" record. I think the order in the inserted and after table is not reliable (might depend on a clustered index).

If you must base your code on having only one record updated at a time, be sure to check @@ROWCOUNT at the very start before SET NOCOUNT ON. I would save it to a local. Just return if the rowcount is zero. Use a raiserror and rollback if it is greater than 1 unless you code for that case.

Also, the UPDATE() function will tell you if the column appears in the update query, not if it is updated to a different value.

Without some means of identifying the before and after of each row, the update trigger query better be simple or very well thought out.

Don't use a cursor in a trigger - bad for performance.

The code for INSERT, UPDATE, and DELETE triggers can sometimes be combined into one trigger using appropriate LEFT (insert or delete) and INNER JOINS (update) between the deleted and inserted tables.

Edited by - rstone on 07/25/2003 1:06:39 PM