How to get a list of triggers

If you are like me, you spend a lot of time in Microsoft SQL Server. Often, a trigger can cause you a lot of grief. Whether it is a recursive trigger that goes into an endless loop. Or a trigger that is updating data in the recordset that you are trying to save (causing problems in your client code).

Here is a quick way to get a list of all triggers in a particular DB

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects WITH(NOLOCK)
INNER JOIN sys.tables t WITH(NOLOCK)
    ON sysobjects.parent_obj = t.object_id 
INNER JOIN sys.schemas s WITH(NOLOCK)
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR' 

You will get a result like this that will list out the triggers and which tables they are tied to.

Using ctAPI to Create Sales Orders
Catalina’s Ever Growing List of API’s for Dynamics SL

Leave a Reply

Your email address will not be published / Required fields are marked *