12

Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every table. Is it possible to get this information?

ADDED:

Here is a good enoght article on mssqltips I have found.

12

You can use the following statement to list all triggers in each table.

EXEC sp_MSForEachTable 'PRINT ''?'' 
EXEC sp_helptrigger ''?'''

Once you find out all the triggers. You can manually change the order using sp_settriggerorder

alt text

  • 2
    Doesn't "isafter" just indicate that the trigger is defined as AFTER <action> rather then INSTEAD OF <action>, rather than dictating anything about the execution order of two or more triggers of the same type? – David Spillett Jan 10 '11 at 12:09
  • @David - yes! you are right – CoderHawk Jan 10 '11 at 12:13
12

IIRC you can not exactly guarantee the order that triggers (with the same definition of what to react to and when) fire for a given action against a table, for any given number of triggers.

You can though if there are three or less, as you can use sp_settriggerorder to set the first to be first, the last to be last, and the one in the middle to have "undefined" order.

If your triggers are sensitive to the order that are executed in this often indicates that your design is getting more complex than it needs to be (usually due to organic growth) and may benefit from some refactoring.

  • +1 you are right, i have to refactor this code (it is complex and has cross links...), but i am at the first step - investigation. – garik Jan 10 '11 at 12:41
5
-- List tables with triggers and their firing order.  By Jackson Jarvis.
SELECT [tbl].[name] AS 'Table'
      ,[trg].[name] AS 'Trigger'
      ,CASE OBJECTPROPERTY([trg].[id] ,'ExecIsFirstInsertTrigger')
            WHEN 0 THEN ''
            ELSE 'X'
       END AS 'Insert First'
      ,CASE OBJECTPROPERTY([trg].[id] ,'ExecIsLastInsertTrigger')
            WHEN 0 THEN ''
            ELSE 'X'
       END AS 'Insert Last'
      ,CASE OBJECTPROPERTY([trg].[id] ,'ExecIsFirstUpdateTrigger')
            WHEN 0 THEN ''
            ELSE 'X'
       END AS 'Update First'
      ,CASE OBJECTPROPERTY([trg].[id] ,'ExecIsLastUpdateTrigger')
            WHEN 0 THEN ''
            ELSE 'X'
       END AS 'Update Last'
      ,CASE OBJECTPROPERTY([trg].[id] ,'ExecIsFirstDeleteTrigger')
            WHEN 0 THEN ''
            ELSE 'X'
       END AS 'Delete First'
      ,CASE OBJECTPROPERTY([trg].[id] ,'ExecIsLastDeleteTrigger')
            WHEN 0 THEN ''
            ELSE 'X'
       END AS 'Delete Last'
  FROM            [sysobjects] AS [trg] WITH (NOLOCK)
       INNER JOIN [sysobjects] AS [tbl] WITH (NOLOCK)
            ON  [trg].[parent_obj] = [tbl].[id]
  WHERE [trg].[TYPE] IN (N'TR')
  ORDER BY
       [tbl].[name] ASC
      ,[trg].[name] ASC
  ;
  • 1
    This looks pretty slick. @garik, does this work on your environment? (BTW, the starting SQL comment should also be part of the code block.) – Nick Chammas Sep 16 '11 at 20:23
  • 1
    @Nick you are right. I am looking around it for a few minutes. – garik Sep 16 '11 at 20:38
  • @Nick by default, these properties is empty. so if execute, for example exec sp_settriggerorder @triggername = 'tr_xxx' , @order = 'Last' , @stmttype= 'DELETE' we can see result ('X') in Jackson's query result. Thanks, Jackson. – garik Sep 16 '11 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.