track all changes on tables

[Machiaveli]

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?

 

[collierd]

You would have to create your history table to run in parallel
i.e. when you update/delete/amend etc your table update the history table with the user and all other relevant detail

I think this is the only way

Damian.

 

[Crookshanks]

You should create Triggers, on the tables you wish to track, that write the info to a tracking or audit table (which you also need to create). Within the trigger you can define the values you insert into the tracking table.

 

[Machiaveli]

Hello Crookshanks,

Do you have a example trigger for me to experiment with?

thnx

 

[Crookshanks]

For example:

CREATE TRIGGER updBase
ON tblBase
FOR update AS
BEGIN
INSERT INTO tblTrackBase
SELECT deleted.*, GETDATE() As dTimestamp FROM deleted
END
GO

Now, the table deleted copies the row of the table on which the trigger is created (tblBase) before a DELETE or UPDATE affects that row. In this case, on an update, the old row and date of change are written to a tracking table.

You could change the events (update) that trigger this query. In addition to the deleted table, there is an inserted table, containing the new values after an update or insert, that you also can use.

 

[Machiaveli]

Hi Crookshanks,

Thnx for the example.

I've tried it out, but there is still one thing that's unclear to me.

After the insert into statement, what or where does the select deleted.* comes from? What has been deleted?

Could you enlighten me?

 

[Machiaveli]

Hi Crookshanks,

I tried it, but i can't get it to work. I copied the following table structure where the updated, inserted, deleted records/values must go.

CREATE TABLE [Log] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Tablename] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Columname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[ValueId] [int] NOT NULL ,
[Oldvalue] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[Newvalue] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[DateChanged] [datetime] NOT NULL ,
[ChangedBy] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK__Log__1B0907CE] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO



Could you help me?