Status of trigger : enabled / disabled

Forum for users of SQL Compare schema synchronization utility

Moderators: JonathanWatts, Chris Auckland, David Atkinson

Status of trigger : enabled / disabled

Postby rwillemain » Tue Jan 05, 2010 12:46 am

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
Posts: 5
Joined: Mon Jan 04, 2010 11:55 pm
Location: Louisville, KY USA

Postby Anu Deshpande » Wed Jan 06, 2010 11:58 am

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.
Anu Deshpande
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby FrankKalis » Mon Jan 18, 2010 9:44 am

+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
Posts: 5
Joined: Tue Mar 25, 2008 2:18 pm

trigger state

Postby rwillemain » Sat Feb 11, 2012 9:24 pm

Thank you very much for the query to derive thsi information.
It is appreciated and vgery useful.
Sincerely, Rick
Posts: 5
Joined: Mon Jan 04, 2010 11:55 pm
Location: Louisville, KY USA

Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests