October 9, 2009 at 1:37 pm
I am looking for a simple script that can run nightly to check which triggers are enabled/disabled. Off and on we will do some work that requires disabling a trigger and then forget to re-enable it after the work is completed. That can cause problems that we don't catch until it is too late. So I would like to monitor the status of existing triggers.
Obviously the triggers are in the sysobjects table but the status of enabled or disabled does not appear to be in that table. Does anyone know what would give me that information?
With the differences between SQL Server 2000 and 2005 I suspect that I will need two different scripts.
Thank you much.
~Ellen
October 9, 2009 at 2:15 pm
Don't know how you'd do it in 2000. In 2005 sys.triggers has an is_disabled column. I don't see anything in sysobjects that tells you.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2009 at 2:37 pm
I don't think you can disable a trigger in SQL 2000.
2005, sys.triggers.
October 9, 2009 at 7:12 pm
Tiggers can be disabled in 2000.
ALTER TABLE tablename DISABLE TRIGGER triggername.
MJ
October 9, 2009 at 7:28 pm
Here's the SQL 2005 version:
select [name], is_disabled from sys.triggers
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 10, 2009 at 1:34 pm
MANU-296622 (10/9/2009)
Tiggers can be disabled in 2000.
makes 'em less bouncy 🙂
---------------------------------------------------------------------
October 10, 2009 at 9:53 pm
george sibbald-364359 (10/10/2009)
MANU-296622 (10/9/2009)
Tiggers can be disabled in 2000.makes 'em less bouncy 🙂
Yeah, but they're still "fun, fun, fun, fun, fun!"
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 12, 2009 at 5:08 am
For SQL2000, looking at the status column in sysobjects, it appears (no guarantee) that the 12th bit from the right is set when a trigger is disabled. This means masking the int value to find out whether that bit is set. I looked at this by capturing the status value for a trigger, then disabled the trigger and compared the value of the status columns at the bit level.
October 12, 2009 at 8:27 am
Thank you all. The SS2005 view makes it simple! Now if I could snag the definition of that view [sys.triggers] I wouldn't have to rewrite the code to look at the SQL Server 2000 system tables.
select substring(t.name,1,25) as TableName, substring(tr.name,1,35) as TriggerName,
case tr.is_disabled when 0 then 'NOT DISABLED' else 'DISABLED' end
from sys.triggers tr, sys.tables t
where tr.parent_id = t.object_id
order by 1, 2
~Ellen
October 12, 2009 at 8:33 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply