Trigger in Microsoft SQL Server 2008: Where are my triggers?

WebTechRiser.com > SQL Server > Trigger in Microsoft SQL Server 2008: Where are my triggers?

 In Microsoft SQL Server 2008, DDL trigger and DML trigger are found in two different places. They are shown in the following two images.

Look for your DDL triggers under Programmatically
Look for your DDL triggers under Programmatically
Look for your DML triggers under your table name
Look for your DML triggers under your table name

The following MSSQL script will list all your triggers you created in your database tables along with some other useful pieces of info like, isupdate, isdelete, isinsert, isafter, isinsteadof, status showing if they are enabled or disabled.

SELECT  table_name = OBJECT_NAME(parent_object_id) ,
        trigger_name = name ,
        trigger_owner = USER_NAME(schema_id) ,
        OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate ,
        OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete ,
        OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert ,
        OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter ,
        OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof ,
        CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')
          WHEN 1 THEN 'Disabled'
          ELSE 'Enabled'
        END AS status
FROM    sys.objects
WHERE   type = 'TR'
ORDER BY OBJECT_NAME(parent_object_id)
Category SQL Server
Tag:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.