Friday, 6 January 2012

Viewing Trigger Order (First and Last)

When I am going to see the trigger execution level, I find several article related to change the SQL server trigger execution lave by sp_settriggerorder

But I have a basic question in my mind, I have a table named Table-test and it contains 4 triggers when I am going to see the sp_helptrigger Table-test it display all the related trigger name. But I want to kanow which one is going to run first and which one is going to Last. I don't want to change any order.

 

I this this SQL statesment help you to understand which trigger is going to execute first and which one is going to execute last.

 

SELECT  name TriggerName,

                ROW_NUMBER() OVER (Order by modify_date Desc) ExecutionOrder

FROM     sys.triggers

WHERE  parent_id = object_id('Table-test')

ORDER BY modify_date DESC

 

Hope it is Informative.

 

Posted By: MR. JOYDEEP DAS

 

 

 

3 comments:

  1. Obviously informative...
    This is excellent work...
    Expecting more without waiting more.

    ReplyDelete
  2. Sorry, but the above code is not correct, which is a shame that it ranks so high in a Google search. If you want to see the result of a call to sp_settriggerorder, then use this.

    -- REVIEW DATABASE TRIGGERS TO SEE WHICH ONES HAVE EXPLICIT TRIGGER EXECUTION ORDERING
    -- MSDN REFERENCE: http://msdn.microsoft.com/en-us/library/ms176105.aspx
    SELECT o.name as [TableName]
    ,t.name as [TriggerName]
    ,CASE WHEN OBJECTPROPERTY(t.[object_id], 'ExecIsFirstInsertTrigger') = 1 THEN 'YES' ELSE '' END as [ExecIsFirstInsertTrigger]
    ,CASE WHEN OBJECTPROPERTY(t.[object_id], 'ExecIsLastInsertTrigger') = 1 THEN 'YES' ELSE '' END as [ExecIsLastInsertTrigger]
    ,CASE WHEN OBJECTPROPERTY(t.[object_id], 'ExecIsFirstUpdateTrigger') = 1 THEN 'YES' ELSE '' END as [ExecIsFirstUpdateTrigger]
    ,CASE WHEN OBJECTPROPERTY(t.[object_id], 'ExecIsLastUpdateTrigger') = 1 THEN 'YES' ELSE '' END as [ExecIsLastUpdateTrigger]
    ,CASE WHEN OBJECTPROPERTY(t.[object_id], 'ExecIsFirstDeleteTrigger') = 1 THEN 'YES' ELSE '' END as [ExecIsFirstDeleteTrigger]
    ,CASE WHEN OBJECTPROPERTY(t.[object_id], 'ExecIsLastDeleteTrigger') = 1 THEN 'YES' ELSE '' END as [ExecIsLastDeleteTrigger]
    FROM sys.objects o
    JOIN sys.objects t
    ON o.[object_id] = t.parent_object_id
    AND t.[type] = N'TR'
    AND o.name IN ('Table-test') -- search the triggers on specific tables
    --AND OBJECTPROPERTY(t.[object_id], 'ExecIsFirstInsertTrigger') + OBJECTPROPERTY(t.[object_id], 'ExecIsFirstUpdateTrigger') + OBJECTPROPERTY(t.[object_id], 'ExecIsFirstDeleteTrigger') > 0 -- only return triggers explicitly executing first
    --AND OBJECTPROPERTY(t.[object_id], 'ExecIsLastInsertTrigger') + OBJECTPROPERTY(t.[object_id], 'ExecIsLastUpdateTrigger') + OBJECTPROPERTY(t.[object_id], 'ExecIsLastDeleteTrigger') > 0 -- only return triggers explicitly executing last
    ORDER BY o.name
    ,t.name

    ReplyDelete