Reasons for objection to SQL triggers that insert data into other tables?

Question

I'm being told by a person with some authority in our company that it's a "database no-no" to create triggers in a database that change rows in another table.

I've used this technique to create default initial configuration, auto-maintaining audit logs, and various other things that would have been a nightmare to consistently maintain inside the heterogeneous applications that connect to that database. For over a decade, I've read that this as an appropriate way to centralize relationship constraint maintenance and get the responsibility out of the applications interacting with the data.

As such, my BS meter is pegging with this. Am I missing something fundamentally wrong with that technique that makes it a bad practice in general?

Answer 1

I'm still looking for that book in which "best practice" is defined - I think I'll find it next to "The big book of database no-nos".

Personally, I've encouraged teams to avoid triggers where possible. I've had bad experiences with them - I agree that hitting oneself with a hammer doesn't make all hammers evil, but triggers are more like nail guns than hammers - the opportunity for harm is definitely there.

Triggers are "side effects" - your explicit intention is to insert a record in a table; as a side effect of this intention, a whole bunch of other stuff happens. Most teams I work with don't have "the database guy" - developers work across multiple layers, and keeping all the side effects in their brain is taxing. This creates the opportunity for bugs and performance problems - not because someone is stupid or lazy, but because the technology has been made more complex.

There are problems which are best (or even only) solved through triggers - enforcing referential integrity is a classic. There are problems which are often solved through triggers which make me nervous - especially when they reflect business rules, or the use of audit table. There are problems which - in my view - are too close to the nail gun trigger to be solved by triggers e.g calculations in the business domain (e.g. calculating the tax amount for a sale).

Answer 2

Not all triggers that insert rows into another table are bad, for example a trigger than keeps a previous version of the row in an AUDIT table, as you say. But without knowing the particulars, I do suspect that your "default initial configuration" created via trigger might be better done in a stored procedure, because the SP is more self-documenting.

Idiosyncractic side-effects are to be avoided -- and by 'idiosyncractic' I mean one person's idea of a short-cut that circumvents the tried-and-true standard way of doing something. They make ongoing maintenance difficult and can be booby traps for the unwary programmer who happens along later.

Answer 3

I think it's similar to the admonition to avoid goto statements in structured programming. You should look pretty hard to find a "better" answer than putting a bunch of DML into triggers, because it's easy to shoot your foot off by mishandling triggers, but sometimes that's the best answer for the problem at hand.

My experience is mostly in SQL Server. Older versions didn't have change tracking or cascading referential integrity, so you might have had to write triggers to handle logging and referential integrity management yourself in the bad old days. Now there are superior tools for both these functions that are built right into the platform. They're "cleaner" than DML-laden triggers, but still allow you to keep those functions centralized.

Even so, there are still cases where writing code in a trigger gets the job done best. Don't let dogma stop you from doing your job, if you conclude through analysis that triggers are the tool you need.

Answer 4

It may be a company policy, but it is not a definitive no-no. The problem with doing it unless you know and control the database is that the amendments of other tables may be inefficient ( and this can often be difficult to identify as an issue ), and there is a danger of them cascading - that is the amendment of this table fires another trigger which may update another table ....

So I would not call it a no-no as such, but something to be done with care.

Answer 5

It's personal preference. In my opinion it's bad practice, though. It can be a bit unruly to manage a db that has triggers on tables updating other tables, which raises another trigger to update another table, etc..

To me, makes more sense to wrap all of the functionality into a stored procedure so all the logic is in one place.

To each their own, though.

Answer 6

If you are careful with your trigger code, there is nothing inherently bad about it. Some people get bitten by bad trigger code and then decide that triggers are bad (eventhough it was the bad trigger code that was the problem). They then generalize this as, "never use triggers".

The other problem is....

Using the audit tables as an example, suppose you have a stored procedure that updates a table AND puts data in to an audit table. Now suppose you write trigger code to put data in to the audit table. You could end up with duplicate audit data.