I have a trigger on a table that should never be disabled. It performs certain checks and there have been occasions when other developers have disabled it to get around it. This is not good so I want to be able to turn off trigger disablement on this table alone. Is this possible? If not, any suggestions please. thanks.

WORKAROUND: Seems from peeps responses that it's not possible to prevent it. Maybe getting an alert would be suitable. This is a good article, shame it doesn't work with EventData though. Maybe in 2008, this is resolved:

+2  A: 

No. Disabling is an admin operation and as such can not be prevented. You can check whether triggers are disabled by code.... but not prevent disabling.

Makes sense, even for example for developers (during debugging).

Maybe just checking is the way forward. i.e. Report them using a DDL trigger or something. But apparently the EVENTDATA doesn't work 100% of the time. see my link above. Thanks though, got me thinking.
+7  A: 

You don't have a technical problem, you have a social problem.

Who are these "other developers"?

Why are they adjusting the triggers?

What is their purpose?

What's wrong with the trigger?

You should talk to them and learn what their problem is.

Don't waste time on looking for a technical "solution" that will only make the problem worse or more complex. Find the people. Talk with them.

Yes i agree, in an ideal world people would listen and wouldn't cut corners. But that's reality. In this situation, we have documentation on how to deal with getting around the trigger, rather than disabling it (i.e. set a flag) but obviously certain developers know better.
And remove DDL permissions if it's production.
@HAdes: I have no idea what your comment means. In the **real** world, people do not cooperate (hence your question) and you need to talk with them to get them to cooperate. In some fantasy world, documentation might help. In the **real** world you have to really talk with the real people and find out why they're really disabling the trigger.
So why have any controls in place whatsoever? If all we ever need to do is talk to people to ask them to cooperate then we can just tell them to not drop databases/tables/sps or not to go and delete files from the file server and we can just remove permissioning completely, allowing everyone to roam freely. In my opinion, sometimes you have to enforce rules.
@HAdes: You **must** enforce the rules. You enforce the rules by **talking**. Find out why they're trying to break the rules. Find out if the rules are wrong. You enforce the rules by meeting with people and assuring that they understand the rules and are able to follow them. I **never** said there are no rules. I said you must **talk** with people.
It's good to talk, i agree. Just not in this situation.
@HAdes: Since there are **no** technical means, you **must** talk.
+1  A: 

Maybe You can create another user, grant select/insert/update/delete on additional tables to new user. Or, You can revoke access to triggers to your user, which owns tables.

+4  A: 

Any solution you put in place can be disabled by developers anyway, such as DDL triggers

The only solutions are

  • remove rights to disable the triggers
  • sack/shoot/hurt the developers if they won't change
+1 - I successfully used the DDL triggers - not that I disabled any DDL changes but also logged the attempts and reviewed on daily basis. Yes, simply revoking permissions is the right thing to do but political situations may not allow this.
+3  A: 

Developers should not have admin rights to a production database. They should not be able to disable triggers because they should not have the rights on prod to do anything except select.

What is the problem with the trigger(s) that people feel the need to disable it? It is badly written? Does it not handle multiple rows correctly? Does it prevent something from happening that they need to happen?

In this situation, we have documentation on how to deal with getting around the trigger, rather than disabling it (i.e. set a flag) but obviously certain developers know better.

This indicates to me that the trigger itself is a problem. Rewrite it so there does not need to be a workaround.

Based on a comment

Add an over-ride trigger field to your data. When you want to override it, send in a value of 1, if the inserted table has a value of one, then have an if stament that ignores the parts of the trigger you need ignored for those records but that then goes on and does the other trigger operations. At the end reset the value of the override field to null.

Alternatively, put the audit triggers in separate triggers, so when they disable the one they need to temporarily disable, the audit triggers still run.

Or take the "sometimes we run it stuff" out of the trigger altogether and add it to the processes that insert records where you want it to run and not to the others.

We need logic in the trigger which prevents data from being edited after a date set in another table. Sometimes, however, at management requests we have to override this and allow editing. Disabling the trigger then means we lose the other stuff in the trigger, such as auditing. I can't see how this could be re-written any other way.
+2  A: 

I tend to agree with HLGEM's comment. You are probably doing the wrong thing in a trigger and that's why people feel the need to circumvent it. So get rid of it or rewrite it. I say "probably" because almost all triggers are unnecessary and triggers are usually a poor place to put data manipulation code. Triggers that enforce business rules without modifying data are reasonable enough. Triggers that actually do UPDATEs, INSERTs and DELETEs are generally much more trouble than they are worth and can nearly always be replaced with better alternatives.


Whenever possible, use constraints, and make sure that they are trusted.