Adventures of a Software Engineer

Category: sql

  • Fast query, but slow from Stored Procedure / Function- How query optimizer can screw your day

    How parameter sniffing can destroy your query performance

  • 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