[TRIGGER Q] Best way to audit changes to a table..

I have a question about triggers. I have a table that has around 25 tables, and whenever any field is changed (INSERT, UPDATE or DELETE), I need to save this information into an audit table.

What is the best way to store this kind of information in the most optimal way?

1. To have all columns as the original table, along with the change date + time --- this way I have only one record per change, but almost each field is being saved again (redundant data!)
2. To have a table wih these columns:
   • Field Name
   • Field Value
   • Action (I, U, D)
   • Action User
   • Action Date

  But this sounds like a silly design as each field will have a different record!!!

I'd appreciate any guidance, thanks!

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Mon Jul 31 2000 - 00:00:00 CDT