Trigger to audit table changes

Question

I have created a trigger to audit changes to a table:

CREATE TRIGGER [dbo].[iudt_AutoAuditChanges] 
   ON  dbo.CPTCategoryMaster
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    Declare @v_AuditID bigint

    IF OBJECT_ID('dbo.AutoAudit','U') IS NULL BEGIN
        CREATE TABLE [dbo].[AutoAudit]
        (   [AuditID] bigint identity,
            [AuditDate] DateTime,
            [AuditUserName] varchar(128),
            [TableName] varchar(128) NULL,
            [OldContent] XML NULL,
            [NewContent] XML NULL
        )

        ALTER TABLE dbo.AutoAudit ADD CONSTRAINT
        PK_AutoAudit PRIMARY KEY CLUSTERED 
        (
            [AuditID]
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

        CREATE NONCLUSTERED INDEX [idx_AutoAudit_TableName_AuditDate] ON [dbo].[AutoAudit] 
        (   [TableName] ASC,
            [AuditDate] ASC
        )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    END

    Select * Into #AuditDeleted from deleted
    Select * Into #AuditInserted from inserted

    While (Select COUNT(*) from #AuditDeleted) > 0 OR (Select COUNT(*) from #AuditInserted) > 0
    Begin

        INSERT INTO [dbo].[AutoAudit]
            ( [AuditDate], [AuditUserName], [TableName], [OldContent], [NewContent])
        SELECT 
            GETDATE(),
            SUSER_NAME(),
            [TableName]=object_name([parent_obj]),
            [OldContent]=CAST((SELECT TOP 1 * FROM #AuditDeleted D FOR XML RAW) AS xml),
            [NewContent]=CAST((SELECT TOP 1 * FROM #AuditInserted I FOR XML RAW) AS xml)
        FROM sysobjects 
        WHERE
            [xtype] = 'tr' 
            and [name] = OBJECT_NAME(@@PROCID)

        Set @v_AuditID = SCOPE_IDENTITY()

        Delete from AutoAudit
        Where AuditID = @v_AuditID
            AND Convert(varchar(max),oldContent) = Convert(varchar(max),NewContent)

        Delete top(1) from #AuditDeleted
        Delete top(1) from #AuditInserted

    End
END

This produces XML data like:

row CPTCategoryId="1" CPTCategoryName="NEW CHARGE" Remarks="" Status="1" CreatedBy="11" CreatedDate="2014-05-01T12:50:16.947" ModifiedBy="11" ModifiedDate="2014-05-01T14:20:47.793" LockVersion="0" 

How can I separate the column data (CPTCategoryId, CPTCategoryName, Remarks... etc) for display in a grid view? Or is there a better way to implement this requirement?

Answer 1

You can catch values for every inserted column in a row and save them into the table where you save other audit data.

There are third party tools, such as ApexSQL Audit, that create such triggers, captures data (for UPDATEs both old and new) and shows reports.

When a row is inserted into a table with the following columns:

All column values are shown as separate columns

You can find more useful info here: Auditing triggers in SQL Server databases

Disclaimer: I work for ApexSQL as a Support engineer

Answer 2

I really like your idea of inserting XML before and after images of the changed data. IMHO, it is not light weight enough for a trigger. I've changed the whole WHILE loop to the following (sorry for the different names)

insert into audit (table_name, old_content, new_content) 
select 
  @table_name,
  case when d.table_id is null then null else (select d.* for xml raw) end,
  case when i.table_id is null then null else (select i.* for xml raw) end
from inserted as i
  full outer join deleted as d on i.table_id = d.table_id

More details are at yact on github.

Update on 11 July 2016

The old_content and new_content columns contain the XML representation of the before and after images of the row.