CODEWITHSUNDEEP
sqldatabasetriggers
Tobias Funke
Tobias Funke

Reputation: 1814

Why should we not include business logic in database triggers?

The business side of my company would like to implement 20 new triggers with very specific business logic. They are also constantly coming back with changes to old triggers. The logic is just group structure depending on parameters 99% of the time.

I have tried time and time again to stop this, but they are used to this process now and expect it. If I disagree, I am told that I still have to implement these.

I need to outline to them in non-technical terms that they will understand why we should not implement these triggers and move these tasks to be developed within our application.

So far I have told them:

  1. Severely high risk of data being lost/overwritten
  2. High DB cost in $
  3. Unnecessary work that could be automated via the application

Are there any other items that could be added to this list?

Apologies if this is not the right place to ask this question.

Thanks!

  1. Severely high risk of data being lost/overwritten
  2. High DB cost in $
  3. Unnecessary work that could be automated via the application

Upvotes: 1

Views: 3064

Answers (2)

Erwin Smout
Erwin Smout

Reputation: 18408

The three reasons you gave are wrong, and betray a pure coder perspective :

  1. Severely high risk of data being lost/overwritten

    (you don't have that risk if your stuff is to be coded in a language that just happens not to be named SQL ?)

  2. High DB cost in $

    (you don't have that risk if your stuff is to be coded in a language that just happens not to be named SQL ?)

  3. Unnecessary work that could be automated via the application

    (Automating your stuff in a language that just happens not to be named SQL is not a process that involves doing analysis and writing implementation code ?)

Triggers are a useful construct for at least one purpose : for guarding data integrity with respect to all integrity rules that aren't supported by SQL declaratively. See "Advanced Mathematics for Database Professionals", chpt 11 for extensive detail.

All that said : designers should beware of attaching [execution of] code to, say, INSERT INTO CUSTOMER for reasons of, say "that happens only if we register a new customer, and all new customers should be getting a welcome letter (or some such)". Consider you taking over another company and all its customers are to be integrated in your database. Ouch. Ill-inspired uses of triggers can often be traced down to "attaching business meaning too eagerly to a db operation happening". This particular stuff indeed does belong in the part where the "business transaction/business function" is coded, which may be application code, or which may be a callable stored procedure (which is distinct from triggers).

Upvotes: 4

Neville Kuyt
Neville Kuyt

Reputation: 29639

I really dislike triggers for business logic, so this may be more of a rant than an answer...

  1. Triggers create a risk of performance problems and locking. There's nothing to stop you from writing triggers with horrible performance characteristics, or even more entertainingly triggers which trigger other triggers which trigger the original trigger.

  2. Triggers are "side effects", and for most humans, side effects often lead to bugs. For instance, if someone is working on the code for persisting a new business entity, and that code causes a trigger to fire, they may not consider that in their code and thus cause a bug.

If your answer is "well, don't do those things":

  1. Triggers are really hard to test. You have to test every single path that could possibly cause the trigger to fire, and on non-trivial applications, that's usually impossible.

Upvotes: 2

Related Questions