Need Input On Trigger Based Database Auditing

Hi

I am building a pretty simple intranet application where we need to be able to track changes to tables. The tracking feature do not need to be very advanced, we just need to see who changed something and what it was. Therefore I decided just to use a trigger based solution, but need some input/advice since my SQL skills is somewhat lacking.

Consider the following (mock-up) schema:


-- My content table
CREATE TABLE [Content](
[ContentGuid] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
[Data] [nvarchar](4000) NOT NULL,
[ChangedBy] [nchar](10) NOT NULL,
[MaybeNull] [int] NULL
)

-- My history table
CREATE TABLE [History](
[ChangedTable] [nvarchar](50) NOT NULL,
[ReferenceGuid] [uniqueidentifier] NOT NULL,
[ChangedBy] [nchar](10) NOT NULL,
[ChangedOn] [datetime] NOT NULL DEFAULT (getutcdate()),
[IsDelete] [bit] NOT NULL DEFAULT ((0)),
[Changes] [xml] NOT NULL
) ON [PRIMARY]

-- My insert/update trigger
CREATE TRIGGER [RecordChangeOnInsertUpdate]
ON [Content]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Changes xml
SET @Changes = (select * from [inserted] for xml raw ('Content'), elements xsinil)

INSERT INTO [History]
([ChangedTable]
,[ReferenceGuid]
,[ChangedBy]
,[ChangedOn]
,[IsDelete]
,[Changes])
SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 0, @Changes
FROM [inserted]
END

-- My delete trigger
CREATE TRIGGER [RecordChangeOnDelete]
ON [Content]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Changes xml
SET @Changes = (select * from [deleted] for xml raw ('Content'), elements xsinil)

INSERT INTO [History]
([ChangedTable]
,[ReferenceGuid]
,[ChangedBy]
,[ChangedOn]
,[IsDelete]
,[Changes])
SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 1, @Changes
FROM [deleted]
END


I have decided to use the "one history table for all table changes" method. The changes for a row is stored in a xml column which obviously limits the total size of columns in a table being tracked, but that is not a problem in my application. In general I like this set up, since I will be able to change the schema continuously without having to change the triggers, and since the application will probably evolve a lot over the coming months this is pretty important to me.

EDIT: I should add that all the tables I will be tracking have a uniqueidentifier column. This makes it possible to related table rows in the different tables being tracked with their history.

A few concerns with the above SQL:

- Can the inserted/deleted table change between "SET @Changes = (select * ..." and the "INSERT INTO ..." statement, such that the data is not valid? If so, how to work around that?

- If I were to (hypothetically) perform a "UPDATE [Content] SET [Data] = 'something'", not only is my update trigger called once for each row updated, but the XML added to the inserted row in the history table ([History].[Changes]) represent all the rows updated in the batch update. How do I get around this?

Are there any other issues I should be aware of?

Regards, Egil.