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 Deshpande]

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.
_________________
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com

[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?
_________________
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org

[rwillemain]

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