How To Use Triggers to Track Changes in MySQL | Hacker News Reader

How To Use Triggers to Track Changes in MySQL

codespatter.com

14 pointsedw51911 comments

This is a maintenance nightmare. Now, whenever you add/change/remove a column, you not only have to update the table definitions, but you have to update the triggers as well. Chances are you'll forget and then updates will start failing all over the place, and the last place you'll look is the triggers. What about deleting the row? Do you clean up the history too? Either way you need a delete trigger as well.

Audit logging like this should be handled on the application level. That way you get to know who did the change, which is generally one of the most important parts of an audit trail.

See "choose a single layer of cleverness" at http://www.loudthinking.com/arc/2005_09.html (can't permalink because he partially recovered from a crash).

"I consider stored procedures and constraints vile and reckless destroyers of coherence."

Finally. Someone comes up with a technical description of how I've always felt. Thank you, dhh.

I don't totally agree with him. I draw the line at stored procedures, triggers, and views; he draws the line at, uh, any relational integrity at all. I think database constraints are good to protect against application bugs.

I wonder why he uses a relational database at all if he's really just using it as a bit bucket.

I read his post and the one he linked to. It's an interesting argument. I've found great usefulness with views and triggers. When working with multiple developers that might mess with your models, I like to have overall control by being the database guy. That is where I am coming from.

Pointing to the fact that a lot of apps being built on Rails/Django/etc. (but not all of them) make sense with something like a doc-oriented DB.

That's all well and good if you have a single layer of business logic operating on a particular database. But in the real world you may have multiple completely separate pieces of business logic, often written in different languages. It that situation, the database itself becomes the only place to put shared code to ensure that rules are followed.

This is where Martin Fowler's distinction between application databases and integration databases comes in. If your app is the only one hitting the database, things are much simpler.

Before doing it with triggers, I had a table that mirrored the main one. So whenever there was an update to any value, all of the data would be copied. If a column was changed in the main I would have to change it in the history table. Now I would change the trigger instead of that other table.

Rows are never deleted from the database, a soft delete is used.

When a user edits something the application does store that in the original table.

How would you propose doing it differently?

I don't disagree, but another reason for using triggers and whatnot for audit trails is that if someone were to get direct access to the database (say, even a DBA), application level audit trails are useless. Of course, the other side of the coin is that if someone has access to the DB, what's to stop them from just disabling the triggers themselves, right?

In my case, triggers and constraints are a Good Thing(TM) because our DBs are touched by different applications running on different platforms (and hardware). Maintenance is actually easier with the database triggers and constraints in place, since shared data access libraries aren't feasible.

I've run into this situation as well. A few apps sharing data. The extra constraints help ensure data integrity.

Reply on news.ycombinator.com