Are database triggers necessary?

Question

In my experience, they are not a good idea 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, if you have logic that must occur every time a new FOO is created in the database, then it may be 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 modified date field of a row.

I'm struggling to figure out whether triggers are necessary and would appreciate any input. If they are necessary, what are the most important issues to consider when implementing them?

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

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

Answer 3

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

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

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

Answer 6

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 7

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 8

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 9

At a high level there are two use-cases for triggers1

1) To make stuff "automagically" happen. In this case triggers cause a side-effect, they change data in ways that were not expected given the (primitive) operator insert, update or delete that was executed and caused the trigger to fire.

The general consensus here is that triggers are indeed harmful. Because they change the well known semantics of an INSERT, UPDATE or DELETE statement. Changing the semantics of these three primitive SQL operators will bite other developers who later in the future need to work on your database tables that do not behave in expected ways anymore when operated upon them with the SQL primitives.

2) To enforce data integrity rules, other than the ones we can deal with declaratively (using CHECK, PRIMARY KEY, UNIQUE KEY and FOREIGN KEY). In this use-case all the triggers do is QUERY (SELECT) data to verify if the change that is being made by the INSERT/UPDATE/DELETE is allowed or not. Just like declarative constraints do for us. Only in this case we (the developers) have programmed the enforcement.

Using triggers for the latter use-case is not harmful.

I'm blogging on that at: http://harmfultriggers.blogspot.com

Answer 10

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 11

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 12

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.

Answer 13

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 14

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 15

This answer applies specifically to SQL Server. (though It may also apply to other RDBMSs I have no idea. I would have preferred to give it as an answer here but that was closed as a dupe of this.)

One aspect not mentioned in any of the answers so far is security. Because, by default, triggers execute under the context of the user that executes the statement that causes the trigger to fire this can cause a security threat unless all triggers are reviewed.

The example given in BOL under the "Managing Trigger Security" heading is of a user that creates a trigger containing the code GRANT CONTROL SERVER TO JohnDoe ; in order to escalate their own permissions.

Answer 16

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 17

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 18

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 19

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

Answer 20

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.