Is there a way to prevent Triggers from being disabled?

Question

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: http://www.simple-talk.com/sql/database-administration/dml-trigger-status-alerts/

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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