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 
