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.