How to make a log on relational-data in SQL-Server?

I need to create in my DB a log, that every action in the program should be written there.

I will also want to store additional data to it for example have the table and row the action w开发者_Go百科as applied to. In other words I want the log to be dynamic and should be able to refer to the other tables in the database.

The problem is, I don't know how to relate all the tables to this log.

Any ideas?

You have two choices here:

1) modify your program to add logging for every db access

2) add triggers to each table in your db to perform logging operations.

I don't recommend one logging table for all tables. You will have locking issues if you do that (every insert, update and delete in every table woudl have to hit this one, bad idea). Create a table for each table that you want to audit. There are lots of possible designs for the table, but they usually include some variant of old vlaue, new value, date changed, and user who did the change.

Then create triggers on each table to log the changes.

I know SQL Server 2008 also has a systemic way to set up auditing, this would be easier to set up than manual auditing and might be enough to lure your company into using 2008.