This shouldn't be important to anyone..... but it may be interesting.When you use a MERGE..Stupid example:; WITH mods ( [TicketableID] , [YearMonth] , [DaysOfWeek] )AS ( SELECT @TicketableID, 2222, 34 UNION SELECT @TicketableID, 2223, 33 )MERGE routes.tbTicketableYEarMonth AS tUSING mods AS s ON s.[TicketableID] = t.[TicketableID] AND s.[YearMonth] = t.[YearMonth]WHEN MATCHED THEN UPDATE SET [DaysOfWeek] = s.[DaysOfWeek]WHEN NOT MATCHED THEN INSERT ( [TicketableID] , [YearMonth] , [DaysOfWeek] , [DaysOfMonth] , [StartDate] , [EndDate] )VALUES ( s.[TicketableID] , s.[YearMonth] , s.[DaysOfWeek] , 0x142850A140 , '22230101' , '22230106' );
It seems that the NOT MATCHED actions are performed first, then the MATCHED actions.I am writing some audit triggers for table which have merge statements applied to them. I'm logging each fire of the trigger in it's own batch and I'm seeing that that the NOT MATCHED inserts all happen before the matched updates.BOL states that quote:
For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify. For more information about setting trigger firing order, see Specifying First and Last Triggers.
ORDER OF MERGE ACTIONS
Posted - 2012-01-25 : 07:52:31
