-- This SELECT statement can be used to get the order of several
-- triggers of a particular type and their count. For details on
-- specifying the type of trigger, see OBJECTPROPERTY help in BOL.
DECLARE @TblName varchar( 128 )
SET @TblName = 'authors'
SELECT o."Name" AS TriggerName,
       OBJECTPROPERTY( o."id" , 'TriggerUpdateOrder') AS UpdateOrder, 
       OBJECTPROPERTY( o.parent_obj , 'TableUpdateTriggerCount') AS TriggerCount 
FROM sysobjects AS o
WHERE o.parent_obj = OBJECT_ID( @TblName ) AND
      OBJECTPROPERTY( o."id" , 'ExecIsUpdateTrigger') = 1 
ORDER BY TriggerCount
GO

-- Another related query that can give the number of triggers for each table
SELECT i."name" ,
       OBJECTPROPERTY( i."id" , 'TableInsertTriggerCount' )  + 
       OBJECTPROPERTY( i."id" , 'TableUpdateTriggerCount' ) + 
       OBJECTPROPERTY( i."id" , 'TableDeleteTriggerCount' ) AS #Triggers
FROM ( 
        SELECT QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) ,
               OBJECT_ID( QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) )
        FROM INFORMATION_SCHEMA.TABLES
) AS i ( "name" , "id" )
WHERE OBJECTPROPERTY( i."id" , 'IsUserTable' ) = 1 And
      OBJECTPROPERTY( i."id" , 'IsMsShipped' ) = 0
ORDER BY i."name"

This page was last updated on May 01, 2006 04:28 PM.