Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 11 down vote accepted

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

share|improve this answer
1  
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 –  Coder Hawk Jan 10 '11 at 12:13
    
+1 thanks a lot! –  garik Jan 10 '11 at 12:52

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.

share|improve this answer
    
+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
-- 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
  ;
share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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