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

views:

answers:

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

Hello folks!

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 was 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?

+1 A:

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.

dar7yl 2009-10-18 19:18:22

My question is not how it should trigger the logging, my question is how to store the long while getting the attached tables' data with it.

Shimmy 2009-10-19 11:23:14

I still don't understand your question. "store the long" is meaningless to me. Perhaps it is a language problem.What data do you want to store from the attached tables? What do you mean by "attached tables"? Attached to what?

dar7yl 2009-10-19 17:13:09

Shimmy 2009-10-19 19:33:44

And BTW, I am planning to do the logging on the client, not by triggers since I have to include the client's IP, computer name and other details in the log entry.

Shimmy 2009-10-19 19:35:22

So now you have an idea of what to put into your table, and where to add the info.For even more efficacy, you may put the client's IP, computer name and other details into another table (once- indexed by clientId), and only store the clientId in your log.

dar7yl 2009-10-20 17:39:41

Let's say i have these tables (i have many more): contact, customer, order, product.I want each log entry should also provide information about a contact and order that is attached to it, you understand?

Shimmy 2009-10-21 00:13:30

You would have to put that information into the table on a case-by-case basis. You may have, for instance, one text field, into which you put your log info, then create a function to extract pertinent data when reporting, depending on the item you are reporting on - which is yet another field.

dar7yl 2009-10-21 10:30:51

I will do a second table that has these cols: logid, tablename, rowid, then i will be able to attach them to the log if they exist, this sounds the best to me.

Shimmy 2009-10-27 03:34:07

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.

HLGEM 2009-10-19 19:04:57

ansaurus

tags:

views:

answers:

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

related questions