469,890 Members | 1,600 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

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 1502
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
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.