Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Logging Data Changes for Auditing

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2012-01-14 : 08:14:26
This is discussed in a number of threads, but I thought as an answer to a FAQ I would consolidate here and show how I record a history of data changes to an "Audit" table.

I create one audit table for each main table that I want to audit, and I only store the earlier versions of the data. I do NOT store the current version of the data, that's available in the Main Table and duplicating that data in the Audit table just doubles the storage requirement.

So let's assume we have a Main Table:

CREATE TABLE dbo.MyMainTable
(
MyCreateDate datetime NOT NULL,
MyUpdateDate datetime NOT NULL,
MyID int NOT NULL IDENTITY (1, 1), -- Primary key
MyCol1 varchar(50) NULL,
MyCol2 varchar(50) NULL,
MyCol3 varchar(50) NULL,
...
) ON [PRIMARY]
GO

-- Default Constraints
ALTER TABLE dbo.MyMainTable ADD CONSTRAINT
DF_MyMainTable_MyCreateDate DEFAULT GetDate() FOR MyCreateDate
GO
ALTER TABLE dbo.MyMainTable ADD CONSTRAINT
DF_MyMainTable_MyUpdateDate DEFAULT GetDate() FOR MyUpdateDate
GO

-- Primary Key Constraint
ALTER TABLE dbo.MyMainTable ADD CONSTRAINT
PK_MyMainTable PRIMARY KEY CLUSTERED
(
MyID
) ON [PRIMARY]
GO

nothing particularly special about that. Then I create an Audit Table. This has two additional columns, and has NO constraints - I want to store the exact data from the Main Table, without alteration. Note also that although the IDENTITY property is specified for the Main Table I do not use that in the Audit Table (but see note below about creating a unique index)

We can now create the table for the Audit Data:

CREATE TABLE dbo.MyMainTable_Audit
(
A_MyAuditAction char(1) NOT NULL,
A_MyAuditDate datetime NOT NULL,


A_MyCreateDate datetime NOT NULL,
A_MyUpdateDate datetime NOT NULL,
A_MyID int NOT NULL,
A_MyCol1 varchar(50) NULL,
A_MyCol2 varchar(50) NULL,
A_MyCol3 varchar(50) NULL
) ON [PRIMARY]
GO

-- Unique Clustered Index
CREATE UNIQUE CLUSTERED INDEX IX_MyMainTable_Audit
ON dbo.MyMainTable_Audit
(
A_MyID,
A_MyAuditDate
) ON [PRIMARY]

GO

this has the two additional columns [A_MyAuditAction] & [A_MyAuditDate]. Note that the Audit Table column which stores the original [MyID] column does NOT have the IDENTITY property. I prefix all the column names in the Audit table with "A_" so that I don't accidentally pick the wrong column when making reports which join the Main Table and the Audit Table, but how you name them is up to you

I keep the exact same ordinal column order between the Main Table and the Audit Table.

To record/log changes in the Audit table we need to create a trigger:

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyMainTable
AFTER UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

INSERT dbo.MyMainTable_Audit
SELECT [A_MyAuditAction] = CASE WHEN I.MyID IS NULL THEN 'D' ELSE 'U' END,-- D=Deleted, U=Updated
[A_MyAuditDate] = GetDate(),
D.*
FROM deleted D
LEFT OUTER JOIN inserted I
ON I.MyID = D.MyID
AND I.MyPrimaryKey2 = D.MyPrimaryKey2
AND I.MyPrimaryKey3 = D.MyPrimaryKey3

...
GO

Every time an existing row in the Main Table is UPDATEd, or DELETEd, the original data is stored in the Audit Table.

Note that I are not using a Column List for the INSERT statement, nor in the SELECT - I am using "SELECT *" - (you can use a column list if you like), the reason is so that the trigger will "break" if a column is added to the Main Table but is forgotten to be added to the Audit Table. For this to work the ordinal sequence of the columns must be the same in the Main Table and the Audit Table.

If you only want to audit some of the columns then use a column list of only the ones you want included.

A trigger needs to be created for each Main Table that has a corresponding Audit Table; the syntax of the Trigger is very simple, so its easy to Find&Replace the table names, or to mechanically-generate the code. Using a consistent naming convention will help a lot though! e.g. using a suffix of "_Audit" for the name of the Audit Table.

Reporting

Reporting the history of changes require combining the Main Table and the Audit Table. I do that like this:

SELECT [Act],
[Date],
* -- Or use an explicit column list MyID, MyCol1, MyCol2, ...
FROM
(
SELECT [Act] = '*',
[Date] = MyUpdateDate, -- or use GetDate() to show "current value"
*
FROM dbo.MyMainTable
WHERE MyID = @MyID
AND MyPrimaryKey2 = @MyPrimaryKey2
AND MyPrimaryKey3 = @MyPrimaryKey3

...
UNION ALL
SELECT *
FROM
(
SELECT TOP 100 * -- First two columns are Action (Update/Delete) and AuditDate
FROM dbo.Audit_MyMainTableName
WHERE A_MyID = @MyID
AND A_MyPrimaryKey2 = @MyPrimaryKey2
AND A_MyPrimaryKey3 = @MyPrimaryKey3

...
ORDER BY A_AuditDate DESC
) AS X
) AS X
ORDER BY CASE WHEN [Act] = '*' THEN 1 ELSE 2 END, X.[Date] DESC, X.MyID, X.MyPrimaryKey2, X.MyPrimaryKey3, ...


Some issues to consider:

Unique Index

In the example above the Unique Index in a combination of the original [MyID] Primary Key from the Main Table and the Audit Date/Time. There may be instances where this is not unique - i.e. you get multiple changed for the same millisecond timing. In such instances you could use a non-unique clustered index, or you could add an IDENTITY Column to the Audit Table, e.g.:

CREATE TABLE dbo.MyMainTable_Audit
(
A_MyAuditAction char(1) NOT NULL,
A_MyAuditDate datetime NOT NULL,
A_MyAuditID int IDENTITY(1,1) NOT NULL,

A_MyCreateDate datetime NOT NULL,
A_MyUpdateDate datetime NOT NULL,
...

CREATE UNIQUE CLUSTERED INDEX IX_MyMainTable_Audit
ON dbo.MyMainTable_Audit
(
A_MyID ASC,
A_MyAuditDate ASC,
A_MyAuditID ASC
) ON PRIMARY


Modification to the DDL of the Main Table

If you change a column in the Main Table from NOT NULL to NULL then do NOT make the same change on the Audit Table - the Audit Table may already contain data that is NULL, and that is the state that that data was in when it was logged!

Logging only certain columns:

Change the trigger to:

INSERT dbo.MyMainTable_Audit
(
A_MyID, A_MyCol1, A_MyCol2, ...
)

SELECT [A_MyAuditAction] = CASE WHEN I.MyID IS NULL THEN 'D' ELSE 'U' END,
[A_MyAuditDate] = GetDate(),
D.MyID, D.MyCol1, D.MyCol2, ...
FROM deleted D
LEFT OUTER JOIN inserted I
ON I.MyID = D.MyID


Purging stale data:

I decide on the retention period for Audit Data in each table. We tend to have "Short", "Medium" and "Long" duration tables which are usually 1 month, 3 months, and 14 months (a bit over a year so we have "this-time-last-year").

Also, I don't necessarily delete data that is older than this. I delete all data that is older than the Retention Time + 3 months, and I delete data that is older than the Retention Time provide that there is a more recent record in the Audit.

So for a table with a 3 month Retention Period, for a given record I will have:

Any rows in the Audit Table that were created in the last 3 months.
If there are no rows in the last three months I will have at most one row that is earlier, provided that it is not more than 6 months old.

This means that users can determine either:

"These are all the changes in the last 3 months"
or
"The records has not been changed in the last 3 months, but this is the last change that was made" [when the last change is 3-6 months ago]
or
"The record has not been changed in the last 6 months"

Audit Tables which have very high volume INSERTS I use some fairly sophisticated looping for the Deletes in order not to cause a single massive delete transaction. (The subject of a different post!)

Further discussion in the forum:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52356 - which includes discussion of a single Audit Table which records the Date, PK, and only the specific columns that have changed, with Before/After data.
Also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139702#546701

Russell said: "I usually add 5 columns to my auditing tables to capture: SYSTEM_USER, host_name(), APP_NAME(), user_name(), getdate()" [but you can leave out any that you are capturing to the Main Table's record]

Edit: SQL script to create audit tables:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141844

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 13:27:32
http://weblogs.sqlteam.com/brettk/archive/2006/08/10/11126.aspx

http://weblogs.sqlteam.com/brettk/archive/2006/11/29/35816.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
   

- Advertisement -