Policies RE database triggers in well-designed applications?

I've heard all kinds of horror stories around gremlins living in database triggers, and--worse--systems being brought down by the addition of a trigger that caused a chain of cascading ones.

I'm considering implementing a strict policy about the use of database triggers. For separation of concerns, the initial thought is to say:


"Database triggers shall be used only for the purpose of capturing and maintaining the audit trail."

• Does anyone have similar policies?

• Are there any strong arguments against such a policy?

Sounds like we hold similar views on trigger usage but I'm very interested to hear the opposite opinions from other DBAs. Personally, I view them as a last resort and have only implemented triggers where there is no other option. The exception being INSTEAD OF triggers against views.

Triggers are often the only option when you're enhancing a 3rd party solution and the extension points are not exposed by the systems API. Where I have full access to source, I've not yet encountered a situation where a trigger was my first thought.

Audit is a good example. In a 3rd party application, triggers may be the only choice. If you have access to source, it is certainly not the only option, though it may be lowest risk/cheapest/easiest (delete as applicable) option. If the database API is well designed, there will be other entry points to add audit.

Despite my thoughts on triggers, I wouldn't impose the strict policy you are suggesting. In my opinion, the DBA vs Developer mentality that persists in many organisations arises from the way this type of guidance is perceived. Try to encourage consultation, rather than dictating.

StackExchange Database Administrators Q#4557, answer score: 8