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

sql-server database triggers history audit
Nathan W picture Nathan W · Aug 9, 2009 · Viewed 8k times · Source

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 e.g., 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 is implemented on top of the database.

Answer

cletus picture cletus · Aug 9, 2009

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.

Related questions

Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema. I'm almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate = OBJECTPROPERTY( …

Read More
How can I do a BEFORE UPDATED trigger with sql server?

I'm using Sqlserver express and I can't do before updated trigger. There's a other way to do that?

Read More
When do triggers fire and when don't they

Pretty general question regarding triggers in SQL server 2005. In what situations are table triggers fired and what situations aren't they? Any code examples to demonstrate would be great. I'm writing a audit based databases and just want to be aware …

Read More