5

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?

7 Answers 7

8

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.

1
  • 2
    One significant advantage of using triggers for audit logging is that they will pick up changes from any source. If you're in the situation where changes could be made from outside your application (data loads, support fixes, malicious updates) then triggers will pick up the change regardless of the source. Making the triggers and audit tables owned by an account other than the application requires an attacker to compromise at least one account other than the application in order to tamper with the audit log data. Apr 19, 2012 at 14:24
5

Overall, triggers have a silent and hidden impact on your data modification performance.

I have seen environments where cascading errors were caused by updating other tables from within a trigger and the updated tables had triggers.

As far as auditting is concerned, a trigger may be used, but if you have the Enterprise edition of SQL Server 2008, or later, you are also able to use Change Data Capture (CDC) functionality. Using CDC functionality eliminates the need to add a trigger to the auditted table and removes the potential for a hidden performance impact.

2
  • I'm a FOSS guy, so no m$ for me. Aside from that, a policy should really be implementation independent, so relying on something that is specific to one vendor would violate the concept of Standard Policy.
    – Bryan Agee
    Aug 11, 2011 at 19:04
  • Granted and that is a valid point. Aug 11, 2011 at 19:05
4

I have just the situation described above by Mark

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.

My example: a c# application written by another developer using LLBL.gen as the ORM. Any change to the data structure that you want the C# code to use implies the recompilation of the entire data structure and overwrite of the previous .dll.
It was easier to add business logic in triggers calling stored procedures than recompile and test the entire application. Success with triggers usually involves the "less is more" adage. Keep the usage to a minimum and replace them with calls from the application when possible. A side benefit of implementing some business logic on the database side is not having to take down the web app when logic changes.

4

In Oracle triggers should be avoided in most situations because they divide a portion of the logic into a separate code area that is harder to debug and only runs as side effect, not directly.

Legitimate exceptions include instead of triggers, integrating with third party code, and setting complex default values. The latter may be the biggest argument for using triggers, but if the business logic is in the database then it shouldn't be necessary.

Business logic should be in packages and all table access should be done through the packages. The package can then implement any logic that would have been done in the trigger including complex default values.

If you must write a trigger at least have it call a procedure in your business logic package so the code is more discoverable and easier to debug/test.

Tom Kyte has a diatribe against triggers. Concluding with this:

Triggers should be the exception, not the rule. They should be used only when you cannot do something any other way. Given the concurrency issues, the problems with doing non-transactional operations in them, and the maintenance problems, triggers are something to use sparingly, if at all.

1
  • +1 for the business logic approach to triggers and describing the overall balanced sense of using triggers. Aug 12, 2011 at 19:11
2

The quick and dirty argument that should come up against such policy is the implementation of business logic.

Mundane auditing of data traffic is only one good use of triggers.

Maintenance of those triggers for business logic makes the use of triggers take on a life of its own, as if it is part of the software lifecycle. For some databases like Oracle, PostgreSQL and SQL Server, such use of triggers in an ACID compliant arena should never be suppressed or relegated if business logic is best implemented in the database.

On the other hand, one DBMS that has issues with ACID compliance in triggers is MySQL. The stored procedure language in MySQL has a rocky history although a measure of success of implementation can be achieved.

Getting away from any specific database and to keep things in perspective:

If referential integrity is to be maintained, let the database's storage engine do it. Why should an application reinvent the wheel of database integrity as part of business logic ? What if a transaction (series of SQL statements) needs to be rolled back and the triggers have already fired off in the middle of the transaction ? Can the audit trail info that was recorded be rolled back ? Does the storage engine allow rollback for trigger level data ? Will you have to design the rollback features independent of the paradigms of standard SQL ? Due diligence of research on these topics will clarify whether triggers are a healthy alternative.

If there are some arithmetic operations you can perform in a trigger that will not slow down the processing of each row in a table, then triggers are OK.

If there is some massaging of data you need to do and you'd rather have a trigger do it instead of at the browser code (PHP, Python, Ruby, whatever) that will not slow down the processing of each row in a table, then triggers are OK.

CONCLUSION

IMHO you should make sure audit trails are handled in transactions rather than in triggers because each recording of a row causes intermittency of database operation, especially when doing bulk processing or in a high-trafficked website. Triggers can accomplish business logic in many (but not all) situations.

2

Everything I've ever learned says "never say never". There will always be cases where you have to go against any established convention and there's just no way around it.

Often times it seems that these situations are caused by older, existing systems; third parties; and architecture already in place. While better solutions may be clear and obvious, the time it takes to implement these solutions is far beyond the time you have available to make them happen (or beyond the resources or authority in some cases).

So, my advise is to--at best--just set general guidelines. Maybe something like:

"Database triggers should be used only for the purpose of capturing and maintaining the audit trail. Care must be taken to avoid the use of triggers when possible."

0

My policy is not to use triggers that modify data. Triggers that don't modify data but enforce integrity rules that can't otherwise be done with constraints are ok. Triggers are just procedures and can always be replaced by other procedures that aren't triggered.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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