History tables pros, cons and gotchas - using triggers, sproc or at application level.

Question

I am currently playing around with the idea of having history tables for some of my tables in my database. Basically I have the main table and a copy of that table with a modified date and an action column to store what action was preformed eg Update,Delete and Insert.

So far I can think of three different places that you can do the history table work.

• Triggers on the main table for update, insert and delete. (Database)
• Stored procedures. (Database)
• Application layer. (Application)

My main question is, what are the pros, cons and gotchas of doing the work in each of these layers.

One advantage I can think of by using the triggers way is that integrity is always maintained no matter what program is implmentated on top of the database.

Answer 1

Triggers are the only reliable way to capture changes. If you do it in Stored Procs or the App, you can always go in and SQL away a change that you don't have a log for (inadvertantly). Of course, somebody who doesn't want to leave a log can disable triggers. But you'd rather force somebody to disable the logging than hope that they remember to include it.

Answer 2

I'd put it this way:

• Stored procs: they're bypassed if you modify the table directly. Security on the database can control this
• Application: same deal. Also if you have multiple applications, possibly in different languages, it needs to be implemented in each stack, which is somewhat redundant; and
• Triggers: transparent to the application and will capture all changes. This is my preferred method.

Answer 3

Triggers are the quickest and easiest way to achieve simple history. The following information assumes a more complex example where history processing may include some business rules and may require logging information not found in the table being tracked.

To those that think that triggers are safer than sprocs because they cannot be bypassed I remind them that they are making the following assumption:

!) Permissions exist that stop users from executing DISABLE TRIGGER [but then permissions could too exist to limit all access to the database except for EXECUTE on sprocs which is a common pattern for enterprise applications] - therefore one must assume correct permissions and therefore sprocs equal triggers in terms of security and ability to be bypassed

!) Depending on the database it may be possible to execute update statements that do not fire triggers. I could take advantage of knowledge of nested trigger execution depth to bypass a trigger. The only sure solution includes security in database and limiting access to data using only approved mechanisms - whether these be triggers, sprocs or data access layers.

I think the choices are clear here. If the data is being accessed by multiple applications then you want to control the history from the lowest common layer and this will mean the database.

Following the above logic, the choice of triggers or stored procedures depends again on whether the stored procedure is the lowest common layer. You should prefer the sproc over the trigger as you can control performance, and side effects better and the code is easier to maintain.

Triggers are acceptable, but try to make sure that you do not increase locks by reading data outside of the tables being updated. Limit triggers to inserts into the log tables, log only what you need to.

If the application uses a common logical access layer and it is unlikely that this would change over time I would prefer to implement the logic here. Use a Chain Of Responsibility pattern and a plug-in architecture, drive this from Dependency Injection to allow for all manner of processing in you history module, including logging to completely different types of technology, different databases, a history service or anything else that you could imagine.

Answer 4

what if an exception comes while inserting data during a trigger..how wud you rollback yr application changes?insertion into main and history table should happen in a single transaction so that atomicity is preserved.Application is the best choice!