Table Audit

jesseleon · June 10th, 2003, 11:33 AM

I have a need to audit a table and have the ability to report on the following;

Field <FieldName> has changed from <OriginalValue> to <NewValue> on <getdate()> by <SYSTEM_USER>

Thanks,
Jesse

Jeff Mason · June 10th, 2003, 12:59 PM

There is no automatic easy way to do this, and there is no way I know to do this after the fact.

You can set up an UPDATE trigger on the table in question. In the trigger, you can insert into a journal/audit table information about those rows where a column in the Deleted pseudotable is not equal to the corresponding column value in the Inserted pseudotable. Note that your audit table should probably also have provision for recording rows in the original table which were inserted or deleted as well as changed.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

jesseleon · July 16th, 2003, 01:44 PM

After trial and error I have found how you can create a trigger on a table that will show what is happening in your tables. This shows that you can log the original value before change, the new value after change, who changed it and when. This sample is based on the authors table in the Pubs db.

/* This is the sample Log table*/
CREATE TABLE [LogTable] (
    [LogID] [int] IDENTITY (1000, 1) NOT NULL ,
    [FromLine] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RECID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED
    (
        [LogID]
    ) ON [PRIMARY]
) ON [PRIMARY]
GO
/* This is the sample TRIGGER TO be added to the authors TABLE IN the Pubs db*/
CREATE TRIGGER [LogTrig] ON [dbo].[authors]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @Myvar char(50), @Myvar2 Char(50)
SET @myvar = (SELECT au_id FROM deleted)
SET @Myvar2 = (Select State FROM authors WHERE au_id = @myvar)
INSERT INTO LogTable
(fromLine,RECID)
SELECT 'Changed State FROM ' + isnull(state,'<NULL>') + ' TO ' + isnull(rtrim(@MyVar2),'<NULL>') + ' BY ' + SYSTEM_USER + ' on ' + convert(char,getdate()),au_id
FROM deleted

Thanks,
Jesse

Jeff Mason · July 16th, 2003, 02:04 PM

Be careful.

The code you posted will not work if more than one author is deleted from the table in the same operation. That is, if I execute:

Code:

DELETE Authors WHERE State='MA';

This deletes all the authors whose state code is 'MA'. If more than one author lives in Massachusetts, you trigger will fail issuing an error message about a subquery returning more than one value...

Instead, your trigger's INSERT into your log table should read something like:

Code:

CREATE TRIGGER [LogTrig] ON [dbo].[authors] 
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO LogTable 
(fromLine,RECID)
SELECT 'Changed State FROM ' + isnull(a.state,'<NULL>') + ' TO ' + isnull(rtrimd.state),'<NULL>') + ' BY ' + SYSTEM_USER + ' on ' + convert(char,getdate()),au_id
FROM deleted d
    INNER JOIN Authors a ON a.au_id=d.au_id;

handling the case where the 'deleted' pseudo-table which is visible in the trigger contains more than one row.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com