Are database triggers evil?

Question

Are database triggers a bad idea?

In my experience they are evil, because they can result in surprising side effects, and are difficult to debug (especially when one trigger fires another). Often developers do not even think of looking if there is a trigger.

On the other hand, it seems like if you have logic that must occur evertime a new FOO is created in the database then the most foolproof place to put it is an insert trigger on the FOO table.

The only time we're using triggers is for really simple things like setting the ModifiedDate.

Answer 1

If there are side effects, it's a problem by design. In some database systems, there is no other possibility to set an autoincrement field i.e. for a primary key ID field.

Answer 2

No, they're actually a good idea. If there's a problem with your specific triggers, then you're not doing them right, but that usually means there's a problem with your implementation, not the concept of triggers themselves :-).

We use triggers a great deal because it places the DBMS-specific activity under the control of the DBAs where it belongs. Users of a DBMS should not have to worry about that sort of stuff.

For example, without triggers, such wondrous things as auto-generated columns wouldn't exist and you'd have to process a function on each row when selecting them. That's likely to kill DBMS performance, far better to create the auto-generated column at insert/update time since that's the only time it changes.

Also, lack of triggers would prevent data rules from being enforced at the DBMS such as pre-triggers to ensure columns have a specific format. Note that this is different from data integrity rules which are generally just foreign key look ups.

Answer 3

Triggers seem to work well for audit logging.

Answer 4

The main problems with triggers are a) they are completely Global - they apply no matter what the context of the table activity; and b) they are stealthy; it's easy to forget they are there until they hurt you with unintended (and very mysterious) consequences.

Which just means they need to be carefully used for the proper circumstances; which in my experience is limited to relational integrity issues (sometimes with finer granularity than you can get declaratively); and never for business or transactional purposes. YMMV.

Answer 5

Indeed, quite often triggers are being misused. Actually in most cases you don't even need them. But that doesn't make them necessarily bad.

A scenario that comes to my mind where triggers are useful is when you have a legacy application for which you don't have the source code and there is no way to change it.

Answer 6

I agree. The problems with triggers is people, not triggers. Although it's more to look at, more to consider and increases the onus on coders checking things correctly, we don't discard indexes to make our lives simpler. (Bad indexes can be just as bad as bad triggers)

The importance of triggers (in my mind) is that...
- Any system should always be in a valid state
- Code to enforce this valid state should be centralised (not written in every SP)

From a maintenance point of view, a trigger is very useful to competant coders and problems for more junior/amateur ones. Yet, these people need to learn and grow somehow.

I guess it comes down to your working environment. Do you have reliable people who learn well and can be trusted to be methodical? If not you seemingly have two choices:
- Accept that you'll have to lose functionality to compensate
- Accept that you need different people or better training and management

They sound harsh, and I guess that they are. But it's the basic truth, in my mind...

Answer 7

Mostly, yes.

The difficulty with a trigger is that it does stuff "behind your back"; the developer maintaining the application could easily not realise it's there and make changes which screw things up without even noticing.

It creates a layer of complexity which just adds maintenance work.

Rather than using a trigger, a stored procedure / routine, can generally be made to do the same thing, but in a clear and maintainable manner - calling a stored routine means the developer can look at its source code and see exactly what's happening.

Answer 8

I know developers who think triggers should always be used where it is the most direct way of achieving the functionality they want, and developers who never will. It's almost like dogma between the two camps.

However I personally completely agree with MarkR - you can (nearly) always write code functionally equivalent to the trigger that will be more perspicuous and therefore easier to maintain.

Answer 9

Not evil. They actually simplify things like

1.Logging/auditing of changes to records or even database schemas

You could have a trigger on ALTER TABLE that rolls back changes in your production environment. This should prevent any accidental table modifications.

---

2.Enforcing referential intrgrity (primary/foreign key relationships, etc) across multiple databases

Answer 10

Triggers are extremely powerful and useful, there are any number of scenarios where a trigger is the best solution to a problem.

They are also a very good "hack" tool. There are often situations where you are not in immediate control of both the code and the database. If you have to wait 2 months for the next major release of your code, yet you can apply a patch to your database immediately then you can put a trigger on a table to perform some additional functionality. Then when the code release is possible you can replace this trigger with your coded version of the same functionality if desired.

At the end of the day, everything is "evil" if you don't know what it's doing. Deciding that triggers are because there are developers that don't understand them is the same as arguing that cars are evil because some people can't drive...

Answer 11

I think they can be evil, but only as evil as anything else in development.

Although I don't really have much experience with them I did have them on a recent project I worked on which has lead me to this conclusion. The problem I have with them is they can cause business logic to end up in two locations, a code library and a database.

I see it as a similar argument with using sprocs. You'll often have developers who are really good at SQL writing business logic into the database, while people who are not will have their business logic elsewhere.

So my rule-of-thumb is look at what the structure of your project is. If it seems viable to have business logic stored in the database then it could be useful to have triggers.

Answer 12

Nah, they're not evil - they're just misunderstood :-D

Triggers have a valid use, but far too often as a retro-hack that ultimately makes things worse.

If you're developing a DB as part of an application the logic should always be in the code or sprocs making the call. Triggers will just lead to debug-pain later on.

If you understand how locking, deadlocking and how DBs access files on disk then using triggers in the right way (for instance auditing or archiving direct DB access) can be really valuable.

Answer 13

To say that they are evil is an exageration but they can cause of mesh. When the firing of one trigger causes other triggers to fire it becomes really complicated. Let's say they are troublesome: http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html

Doing business logic in Oracle with triggers is harder than it seems because of multi concurrency issues. You don't see changes in another session until the other sessions commits.

Answer 14

Triggers have their uses - logging/auditing and maintaining a "last modified" date are two very good uses which have been mentioned in previous replies.

However, one of the core tenets of good design is that business rules/business logic/whatever you want to call it should be concentrated in a single place. Putting some of the logic in the database (via triggers or stored procs) and some in the application violates that principle. Duplicating the logic in both places is even worse, as they will invariably get out of sync with each other.

There is also the "principle of least surprise" issue which has already been mentioned.

Answer 15

With the name Triggers they must be dangerous, but.. We have the 2nd Amendment to protect the right to use them.

Answer 16

Tools are never evil. Applications of those tools can be evil.

Answer 17

Triggers are a good tool when used properly. Expecially for things like auditing changes, populating summarization tables, etc.

Now they can be "evil" if you end up in "trigger hell" with one trigger that kicks off other triggers. I once worked on a COTS product where they had what they called "flex triggers." These triggers were stored in a table as dynamic sql stings are were compiled every time they were executed. Compiled triggers would do a look up and see if that table had any flex triggers to run and then compile and run the "flex" trigger. In theory this sounded like a really cool idea because the product was easily customized but the reality was the database pretty much exploded due to all the compiles it had to do...

So yeah, they're great if you keep what you're doing in perspective. If it is something pretty simple like auditing, summarizing, auto-sequencing, etc, no prob. Just keep in mind the growth rate of the table and how the trigger will impact performance.

Answer 18

I think triggers are not only not evil, but necesasary to good database design. Application programmers think that databases are only affected by their application. They are often wrong. If data integrity is to be maintained no matter whare the data change came from triggers are a requirement and it is foolish to avoid them because some programmers are too ethnocentric to consider that something other than their prized application may be affecting things. It isn't hard to design or test or troubleshoot a trigger if you are a competent database developer. Nor it is difficult to determine that a trigger is causing an unexpected result if it occurs to you (as it does to me) to look there. If I get an error saying a table that I'm not referencing in my sp has an FK error, I know without even thinking about it that trigger is causing the problem and so should any competent database developer. Putting business rules only in the application is the number one cause I have found of bad data as others have no idea that rule even exists and violate it in their processes. Data-centric rules belong in the database and triggers are key to enforcing the more complex ones.

Answer 19

Idea of triggers is not evil, limiting nesting of triggers is evil.

Answer 20

They are definitely not evil. I found triggers precious during refactoring of database schemas, while renaming a column, or splitting a column into two columns or vice-versa (example: name/surname case) and assisting the transition.

They are also very useful for auditing.