SQL Server Triggers - Order of Execution

Does sql server trigger has order of execution?

Oh man - having a lot of triggers on a single table like that...bad juju, I think controlling the execution order is not an approach you want to take. Where there just have to be multiple triggers, they should be doing work that doesn't depend on other triggers to do their work first. They should be more completely isolated.

Before we get there, you have expressions that make no sense in your first trigger...things like

WHEN t1.[RE-COMMIT DATE] =Null

...which you should never see/use when ANSI_NULLS is ON (and which should always be ON).

Also...things like:

IsNull( t1.[Promised Date], Null )

...make no sense either. This says, return the first value...but if that's null...then return the second value. If you get that stuff out of your trigger, it will be easier to understand.

So...how to get everything in a single trigger? I'd start by making a single trigger...that is sensitive to changes in the promised date(s). After all, everything can be seen as being scheduled based on that. That is, make your trigger as obvious as it can be...and delegate the noisy parts to functions, procedures and views as need.

So, I'd drop all those nasty things and start with something simple, like...

create trigger [SetWorkflowDates] on dbo.WORKORDERS for insert, update as
begin

  set nocount on, datefirst 7

  update dbo.WORKORDERS
    set 
      [Ship By]     = dbo.CalcDate('ship',  [Calc Promised Date],[Promised Date],[Re-Commit Date]),
      [Mount By]    = dbo.CalcDate('mount', [Calc Promised Date],[Promised Date],[Re-Commit Date]),
      [A-Powder By] = dbo.CalcDate('powder',[Calc Promised Date],[Promised Date],[Re-Commit Date]),
      [A-Fab By]    = dbo.CalcDate('fab',   [Calc Promised Date],[Promised Date],[Re-Commit Date]),
      [A-Print By]  = dbo.CalcDate('print', [Calc Promised Date],[Promised Date],[Re-Commit Date]),
      [A-C/S By]    = dbo.CalcDate('cs',    [Calc Promised Date],[Promised Date],[Re-Commit Date]),
      [A-Cut By]    = dbo.CalcDate('cut',   [Calc Promised Date],[Promised Date],[Re-Commit Date])
  from
    dbo.WORKORDERS wo
    inner join
    inserted i
    on
      wo.[Work Order #] = i.[Work Order #]
    left outer join
    deleted d
    on
      i.[Work Order #] = d.[Work Order #]
  where
    isnull(i.[Calc Promised Date],getdate()) != 
    isnull(d.[Calc Promised Date],getdate()) 
      or
    isnull(i.[Promised Date],getdate()) != 
    isnull(d.[Promised Date],getdate()) 
      or
    isnull(i.[Re-Commit Date],getdate()) != 
    isnull(d.[Re-Commit Date],getdate())
end

Note that this should make the conditions work for insert or update.

Nice and small and clear and much easier to debug than a whole bunch of triggers.

So...the trigger won't compile until you define that CalcDate function in there...a function that takes governing dates...and computes another date, based on the stage. You're well served to get that out of the trigger itself...just so you can read and understand the trigger without pulling your hair out.

Maybe CalcDate might be something like:

create function dbo.CalcDate
( 
  @stage varchar(8), @calc date, @prom date, @recommit date 
) 
returns date as
begin

  declare @result date =
    case datepart( weekday, @calc )
      when 7 then 
        dateadd( day, -1, @calc )
      when 1 then 
        dateadd( day, -2, @calc )
      else
        coalesce( @recommit, @prom )
     end

   if ( @stage = 'ship' ) return ( @result );

   set @result = dbo.PreviousWorkDay( @result );       
   if ( @stage = 'mount' ) return ( @result );  

   set @result = dbo.PreviousWorkDay( @result );       
   if ( @stage = 'powder' ) return ( @result ); 

   set @result = dbo.PreviousWorkDay( @result );
   if ( @stage = 'fab' ) return ( @result );       

   set @result = dbo.PreviousWorkDay( @result );
   if ( @stage = 'print' ) return ( @result );

   set @result = dbo.PreviousWorkDay( @result );
   if ( @stage = 'cs' ) return ( @result );

   set @result = dbo.PreviousWorkDay( @result );
   if ( @stage = 'cut' ) return ( @result );

   raiserror( 'Unrecognized stage', 16, 1 );

end

So...now I can see that the CalcDate calculates a work stage based on the stage...and it's easy to update when the workflow rules change.

...and then finally, the PreviousWorkDay function subtracts a day...sees if it's a workday, and if not, subtracts another until it is a work day:

create function dbo.PreviousWorkDay( @date date ) returns date as
begin
  set @date = dateadd( day, -1, @date )
  return
  (
    select case datepart( weekday, @date )
      when 7 then dateadd( day, -1, @date )
      when 1 then dateadd( day, -2, @date )
      else @date
    end
  )
end

All the code can then make sense - function names say what they do...they don't try to do too much (or not enough). Fixing and updating is much easier. Plan and simplify at every opportunity.

Note: You should go delete the earlier question. It just upsets SO folks when you ask questions twice because you didn't like the answers the first time. You won't always get kind treatment when you do that.

Set the Priority for two different after insert triggers in SQL Server

Trigger's will be executed in undefined order. But you can set the order of firing through sp_settriggerorder inbuilt procedure.

sp_settriggerorder @triggername= 'Triggername', @order='First', @stmttype = 'Insert';

SQL Server trigger execution in batch updating

Triggers in SQL Server always execute once per batch - there's no option for "for each row" triggers in SQL Server.

When you mass-update your table, the trigger will receive all the updated rows at once in the inserted and deleted pseudo tables and needs to deal with them accordingly - as a set of data - not a single row

SQL Server trigger execution

The behavior is subject to the nested triggers server configuration, see Using Nested Triggers:

Both DML and DDL triggers are nested
when a trigger performs an action that
initiates another trigger. These
actions can initiate other triggers,
and so on. DML and DDL triggers can be
nested up to 32 levels. You can
control whether AFTER triggers can be
nested through the nested triggers
server configuration option. INSTEAD
OF triggers (only DML triggers can be
INSTEAD OF triggers) can be nested
regardless of this setting.

When a trigger on table A fires and inside the trigger table B is updated, the trigger on table B runs immediately. The Table A trigger did not finish, it is blocked in waiting for the UPDATE statement to finish, which in turn waits for the Table B trigger to finish. However, the updates to table A have already occurred (assuming a normal AFTER trigger) and querying the Table A from the table B's trigger will see the updates.

---

Related Topics