Red Gate forums :: View topic - Status of trigger : enabled / disabled
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

Status of trigger : enabled / disabled

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
rwillemain



Joined: 04 Jan 2010
Posts: 5
Location: Louisville, KY USA

PostPosted: Tue Jan 05, 2010 12:46 am    Post subject: Status of trigger : enabled / disabled Reply with quote

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
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 683
Location: Cambridge

PostPosted: Wed Jan 06, 2010 11:58 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
FrankKalis



Joined: 25 Mar 2008
Posts: 5

PostPosted: Mon Jan 18, 2010 9:44 am    Post subject: Reply with quote

+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
Back to top
View user's profile Send private message
rwillemain



Joined: 04 Jan 2010
Posts: 5
Location: Louisville, KY USA

PostPosted: Sat Feb 11, 2012 9:24 pm    Post subject: trigger state Reply with quote

Thank you very much for the query to derive thsi information.
It is appreciated and vgery useful.
Sincerely, Rick
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group