http://www.lumigent.com/go/ud14
Find Data-Recovery and Best Practices white paper from
By Stephen Wynkoop, Microsoft SQL Server MVP
(Founder The SQL Server Worldwide User’s Group)
Tips on Triggers
Encrypting Trigger Definitions
If you want to ensure that other users cannot view the trigger definition, you can use the WITH ENCRYPTION clause. The trigger definition is then stored in an unreadable form.Once encrypted, the definition of the trigger cannot be decrypted and cannot be viewed by anyone, including the owner of the trigger or the system administrator.
-----------------------------------------------------------------------------
Use the INSTEAD OF trigger to replace the standard triggering action
CREATE TRIGGER TableAInsertTrig ON TableA
INSTEAD OF INSERT
AS ...
Use the AFTER trigger to augment the standard triggering action
CREATE TRIGGER TableBDeleteTrig ON TableB
AFTER DELETE
AS ...
Use the FOR trigger to augment the standard triggering action
-- This statement uses the FOR keyword to generate an AFTER trigger.
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR UPDATE
AS ...
-------------------------------------------------------------------------------
Recursive trigger could be turned off and also nested trigger could be turned off.