Audit Tables Using XML Schema

Audit Tables Using XML Schema

Auditing tables can be a complicated procedure if you require a new table for every table being audited. Fortunately the new XML datatype can help to automate this procedure of auditing multiple tables into a single table.

The following trigger template can be used to audit a table into a single table.

First let’s create the table needed to hold the audited values:

CREATE TABLE [Log].[AuditTableHistory](
    [HistoryID] [INT] IDENTITY(1,1) NOT NULL,
    [TableSchema] [VARCHAR](10) NULL,
    [TableName] [VARCHAR](100) NULL,
    [AuditValue] [xml] NULL,
    [DateCreated] [datetime] NULL,
 CONSTRAINT [PK_AUDITTABLEHISTORY] PRIMARY KEY CLUSTERED
(
    [HistoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]


-- Now let's create the trigger

CREATE TRIGGER [Audit].[Trg_Schema_Table]
   ON  [Schema].[TABLE]
   AFTER UPDATE,DELETE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @AuditValue xml

    SET @AuditValue =
    (
        SELECT TOP 1 *
        FROM DELETED
        FOR XML AUTO
    )

    INSERT INTO Log.AuditTableHistory
    (
        AuditValue,
        TableSchema,
        TableName,
        DateCreated    
    )
    SELECT
        AuditValue  = @AuditValue,
        TableSchema = OBJECT_SCHEMA_NAME(parent_obj),
        TableName   = OBJECT_NAME(parent_obj),
        DateCreated = GETDATE()
       
    FROM sysobjects so
    WHERE so.id = @@PROCID
               
END

GO

We can apply this trigger to any table we want just by changing the “ON” statement. It will work for any table. The only difficulty comes in when we change the schema for a particular table. When this happens, we need to know the date/time the table schema changed so we can query the table appropriately.