0

I have the following columns created in my SQL Server table :

Log_id
Old_value
New_value
Module [The module for the transaction]
Reference_id [unique id of the record from respective tbl to associate with transaction history]
Transaction [The type of transaction. Can include, INSERT, UPDATE or DELETE]
Transaction_status [Indicates SUCCESS or FAILURE]
Stack_trace [The detailed stack trace of the error]
Modified_on
Modified_by

What would be the best possible and efficient way to insert the relevant data to the above audit table whenever Insert, update or delete happens.

Please advice. Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anuya
  • 8,082
  • 49
  • 137
  • 222

2 Answers2

0

Best possible en efficient depends on a lot of things: Load on database (how many updates, how big), what are you going to do with the data, etc.

But one way to do it is with triggers: Blog about adding audit trails with triggers and MSDN Documentation

MarcelDevG
  • 1,377
  • 10
  • 10
0

Trigger may have a problem with Modifed_by. This is ASP.NET so most likely a single connection to SQL and only ASP knows Modified_by.

If all the information for the log is already in SQL then trigger.

If the log needs information not already in SQL then best and efficient are not the same.

Two independent statements via a stored procedure would be the most efficient.

But for data integrity wrap the two statements in a transaction so they succeed or fail as a unit. Very little overhead. Again in a stored procedure. Or in the code behind you could use TSQL and transaction but stored procedure is going to be faster and safer.

Also look at indexes on your log. Less indexes is faster insert. A log is where you might want to optimize insert speed over select speed.

paparazzo
  • 44,497
  • 23
  • 105
  • 176