472,102 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,102 software developers and data experts.

track all changes

hello,

I was wondering how to track all changes on tables by using some sort of a
history table.

What i would like is a generic history table where i can see who updated,
inserted, deleted or executed(stored procedures, triggers) what value in
what table with a date when it was occured.

Could somebody help me with this?
Jul 20 '05 #1
1 1570
EzekiŽl (ezekiŽl@lycos.com) writes:
I was wondering how to track all changes on tables by using some sort of a
history table.

What i would like is a generic history table where i can see who updated,
inserted, deleted or executed(stored procedures, triggers) what value in
what table with a date when it was occured.

Could somebody help me with this?


It's a tall order, and you could easily spend too much time on this. If
you are looking for a full-scale solution, I strongly advice you to look
at third-party products. The two I usually quote are Lumigent's Entegra
and Red Matrix' SQL Audit. Lumigent works from the transaction log which
is quite a palatable solution. SQL Audit uses triggers. I have no personal
experience of either products.

But if you are prepared to lower your level of ambition, here is table
that I designed recently:

CREATE TABLE basiclogs2 (
bl2id int IDENTITY,
tablename sysname NOT NULL,
keyvalue1 sql_variant NOT NULL,
keyvalue2 sql_variant NULL,
appname sysname NULL
CONSTRAINT default_bl2_appname DEFAULT app_name(),
hostname sysname NULL
CONSTRAINT default_bl2_hostname DEFAULT host_name(),
moddate datetime NOT NULL
CONSTRAINT default_bl2_moddate DEFAULT getdate(),
moduser sysname NOT NULL,
CONSTRAINT default_bl2_moduser DEFAULT SYSTEM_USER,
CONSTRAINT pk_bl2 PRIMARY KEY NONCLUSTERED (bl2id))

Then in the tables I am interested to track, I have added code to insert
data into that table like:

INSERT basiclogs2 (tablename, keyvalue1, keyvalue2)
SELECT 'thistable', keycol1, keycol2
FROM inserted

That is, I am only logging that a certain row has been changed, but not
what. (We do have a table for this as well, but it logs selected columns.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Nicole | last post: by
5 posts views Thread by Daniel Walzenbach | last post: by
20 posts views Thread by John Sheppard | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.