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], T.name AS TableName, TR.name 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 T.id = TR.parent_obj INNER JOIN
                      scdc.change_tables ON T.name = scdc.change_tables.name
WHERE     (T.xtype = 'U') AND (TR.xtype = 'TR') AND (TR.name LIKE 'tr%')
order by [Status]
Coordinator
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
,t.name AS triggerName
,CASE
     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.