sql

Setting Firing Order Of SQL Triggers

At times there is a need to control order of execution of triggers. This could be due to business requirement but also as a way to control history of a record

--  this trigger should be last in the execution chain
sp_settriggerorder @triggername= '[dbo].[awesome_trigger]', @order='Last', @stmttype = 'UPDATE';  

I usually don’ t see people put any way to prevent trigger nesting, example update trigger updating same records would fire the trigger again.

ALTER TRIGGER [dbo].[Awesome_Trigger]  ON [dbo].[SecretTable]
   AFTER UPDATE
AS 
BEGIN

	-- DO NOT CHANGE
	IF trigger_nestlevel() > 1 RETURN
       
        -- Do something clever
        SELECT 1

END

Leave a Reply

Your email address will not be published. Required fields are marked *