I just learn to set the priority of the triggers to be fired and came across with one system stored procedure which can be used to set force orders of DML and DDL triggers.

EXEC SP_SETTRIGGERORDER 
    @triggername = 'TriggerName' -- Trigger Name
    @order = 'FIRST'  -- First | Last
    @stmttype = 'UPDATE' -- Insert | Update | Delete

Here is small explaination how triggers prioritized.

1. For TWO triggers - Trigger defined with "First" order will be fired first. Trigger defined with "Last" order will be fired last or on second.
2. For THREE triggers - Trigger defined with "First" order will be fired first. Trigger defined with "Last" order will be fired last(on third). Rest trigger without specified in order will be fired in middle order of first and last one, means it will have second order.
3. For MORE THAN THREE triggers - Trigger defined with "First" order will be fired first. Trigger defined with "Last" order will be fired last. Rest triggers without specified in order will be fired in middle order of first and last one and they will have any of order and no guaranty of fix priority oredrs.

Actually i was believe as we can set orders in first, second, third and so on. But we can set them with First and Last priority only. We can not manage orders of more than three triggers at all.

Published under: SQL Server Tips ·  ·  ·  ·