SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Logging Data Changes for Auditing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/14/2012 :  08:14:26  Show Profile  Reply with Quote
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

Edited by - Kristen on 01/17/2012 07:11:49

X002548
Not Just a Number

15586 Posts

Posted - 01/17/2012 :  13:27:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000