Home / server / Questions / 165734
Accepted
NitroxDM
Asked: 2010-07-31 12:22:01 +0800 CST

Trigger automatically gets disabled... why?

  • 772

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?

sql-server-2005

2 Answers

  1. Best Answer

    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.

    • 1

  2. 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.

    • 0