Are database triggers necessary?

211

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?

database triggers

Edit Report

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

answered 2009-01-20T09:46:43.570

Are database triggers necessary?

1 Answer

Your Answer