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

	INSERT dbo.MyAuditTable
	SELECT	[AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,
		[AuditDate] = GetDate(),
		D.*
	FROM	deleted D
		LEFT OUTER JOIN inserted I
			 ON I.MyPK = D.MyPK
GO

quote:

---

Originally posted by Kristen

Nigel's method is straightforward to impliment, but hefty on resources at run time.

I have only once implimented an audit system which stored table / column / OldData / NewData in a single auditing table and it was a nightmare.

We built scripts for each table's triggers, so it did not have the performance issues of a generic script, but it was still a monster. The scripts were massive, the data generated was massive, the data was poorly indexed, and it was a very difficult to report on the data changes in a way that was useful to the users.

Instead we now use one audit table per user data table. Triggers only store deleted/updated rows (i.e. the current record is NOT in the audit table, that's in the actual user data table). Reporting of changes to a row is easy, data volume is modest, purging of old data is easy (in addition to "Keep 6 months worth" it is also easy to do "Keep 6 months worth and at least the last 10 changes"), indexes etc. can be set appropriately on a table-by-table basis, and performance is excellent.

I suppose for an application with some frequently changing columns, and lots of never changing columns, this may be wasteful of audit table space, but on balance its stood us very well over the years.

Our audit tables have two additional columns at the start - a "Type" and an "Audit Date", and then the triggers look something like this:

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

	INSERT dbo.MyAuditTable
	SELECT	[AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,
		[AuditDate] = GetDate(),
		D.*
	FROM	deleted D
		LEFT OUTER JOIN inserted I
			 ON I.MyPK = D.MyPK
GO

Edit: Removed INSERT trigger type - not relevant to this specific code snippet
Kristen

---

exec master..sp_addlogin 'test', 'test'
go
exec <someDB>..sp_grantDBAccess 'test'
go

--connected as user test and using <someDB>: 
select * from information_schema.tables

--connected as admin: 
grant select on <someDB>.SomeTable to test
go
--connected as user test and using <someDB>: 
select * from information_schema.tables

exec <someDB>..sp_dropuser 'test'
go
exec master..sp_droplogin 'test'

SELECT	[Act],
	[Date],
	*	-- Or use an explicit column list Col1, Col2, ... 
FROM
(
	SELECT	[Act] = '*',
		[Date] = MyRecordUpdateDate,	-- or use GetDate() to show "current value"
		*
	FROM	dbo.MyTable
	WHERE	    MyPrimaryKey1 = @MyPrimaryKey1
		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_MyTableName
		WHERE	    A_MyPrimaryKey1 = @MyPrimaryKey1
			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.MyPrimaryKey1, X.MyPrimaryKey2, X.MyPrimaryKey3, ...

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

	INSERT dbo.MyAuditTable
	(
		Col1, Col2, ...
	)
	SELECT	[AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,
		[AuditDate] = GetDate(),
		D.Col1, D.Col2, ...
	FROM	deleted D
		LEFT OUTER JOIN inserted I
			 ON I.MyPK = D.MyPK
GO