Status of trigger : enabled / disabled

rwillemain

In a comparison of what are identical dbs, I can see triggers as such, but in no apparent way can I determine if there are enabled or disabled.
Bummer ! Missed a big problem in this case ... Any ideas ?
Thanks, Rick

Anu D

Thanks for your post.

Unfortunately SQL Compare doesn't display a difference if the trigger is enabled on one database and not on another.

Displaying the difference in trigger status is currently on the wishlist for SQL Compare, and will hopefully be added to a future version. The feature tracking code for this is SC-3996.

As a workaround, you can query the status of all triggers on a database using the following:

SELECT T.[name] as TableName, TR.[Name] as TriggerName,
CASE WHEN 1=OBJECTPROPERTY(TR.[id], 'ExecIsTriggerDisabled')THEN 'Disabled' ELSE 'Enabled' END Status
FROM sysobjects T INNER JOIN sysobjects TR on t.[ID] = TR.parent_obj WHERE (T.xtype = 'U' or T.XType = 'V')
AND (TR.xtype = 'TR') ORDER BY T.[name], TR.[name]

I hope this is helpful.

FrankKalis

+1 on showing the difference in trigger status while comparisons. Could save a lot of time troubleshooting issues.

Is there a way to use the feature tracking code to see what the status is please?

rwillemain

Thank you very much for the query to derive thsi information.
It is appreciated and vgery useful.
Sincerely, Rick