Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a trigger that keeps getting disabled. It will run from months at time without issue then just be disabled. Why?

It would be cool to know: Is there a way I can log when it get's disabled? What is disabling it? Is there a way to set up an email alert that it has been disabled?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Someone has to be disabling it. You can setup a SQL trace that runs in the background looking for the ALTER TRIGGER command.

If you were using SQL Server 2005 and above you could put a DDL trigger on the database to rollback the ALTER statement, then log the command.

With SQL 2000 about all you've got available to you is running a trace.

share|improve this answer
    
SQL Server 2005 has DDL triggers too... technet.microsoft.com/en-us/library/ms191441(SQL.90).aspx –  gbn Jul 31 '10 at 5:21
    
Humm, not sure why I thought those were introduced in SQL 2008. To many damn versions out there. I'll update my answer. –  mrdenny Aug 5 '10 at 21:07

Depending on what other people may have their fingers in your database... another possibility is that someone has a job or stored procedure or a piece of code that is:

Disabling the trigger 
Performing an action 
Enabling the trigger 
(however it fails somewhere before the enable)

-or-

Disabling the trigger
Performing an action
NOT enabling the trigger

You may want to do a quick search through your code and sprocs/jobs for the name of your trigger to see if someone is doing something like this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.