Are database triggers evil? [closed]

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.

Solution 1:

The main problems with triggers are

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

This 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 usually not for business or transactional purposes. YMMV.

Solution 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 database where it belongs. Users of a DBMS should not have to worry about that sort of stuff. The integrity of data lies with the database itself, not the applications or users that use it. Without constraints and triggers and other features in the database, it's left to the applications to enforce the rules and it only takes one rogue or buggy application/user to destroy the data.

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.

Solution 3:

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

Solution 4:

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

Solution 5:

I think triggers are not only not evil, but necessary 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 where 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.