home


Basic trigger information.
Author Nigel Rivett
Every sql statement is a transaction (atomic), a trigger is part of that statement so is also always included in a transaction.
So you don't need explicit transactions in a trigger. 

For each statement the trigger will fire once so if it affects multiple records things like 
SELECT @fld=fld FROM INSERTED 
will end up with one of the records from inserted (probably the "last" affected but ...).

You need to join to inserted/deleted in all your statements using the primary key to get corresponding records. 

update tbl
set fld = i.fld
from inserted i
where i.pk = tbl.pk

v7 and before only implemented after triggers - i.e. any constraints/referential integrity is checked before the trigger fires.
Any errors prevent the trigger from firing.

v2000 introduced "instead of" triggers which fire in place of the statement.
It is up to the trigger to perform any updates so referential integrity violations can be avoided by the trigger.

Note that triggers can affect @@rowcount and @@identity.
How these values are affected is release dependent and should always be tested.


home