List state of CDC triggers

Aug 25, 2011 at 1:46 PM

A useful thing to know is whether you've remembered to enable that CDC trigger before deploying to a live system. Rather than go through the scdc.change_tables entries one at a time, you could just run the code below; BTW, in practise I've prefixed the CDC triggers with trSCDC_ rather than the default tr in order to differentiate them from other triggers in the same database or on the same table, however, here I've replaced the tr prefix for other users' convenience.

SELECT     DB_NAME() AS [Database Name], AS TableName, AS TriggerName, CASE WHEN OBJECTPROPERTY(TR.[id], 'ExecIsTriggerDisabled') 
                      = 1 THEN 'Disabled' ELSE 'Enabled' END AS Status
FROM         sys.sysobjects AS T INNER JOIN
                      sys.sysobjects AS TR ON = TR.parent_obj INNER JOIN
                      scdc.change_tables ON =
WHERE     (T.xtype = 'U') AND (TR.xtype = 'TR') AND ( LIKE 'tr%')
order by [Status]
Aug 25, 2011 at 2:43 PM

Take a look at the catalog view sys.triggers it will give you what you are looking for in an easier query.

SELECT OBJECT_NAME(t.parent_object_id) AS tableName
, AS triggerName
     WHEN tr.is_disabled = 0 THEN 'Enabled'
     ELSE 'Disabled'
END AS triggerStatus
FROM sys.triggers AS t
WHERE t.is_ms_shipped  = 0
     AND t.type = 'TR';
Not a bad idea on the SCDC prefix, I might look into that.