SQL Server history table – populate through SP or Trigger?

by

Triggers.

We wrote a GUI (internally called Red Matrix Reloaded) to allow easy creation/management of audit logging triggers.

Here’s some DDL of the stuff used:

The AuditLog table

CREATE TABLE [AuditLog] (
    [AuditLogID] [int] IDENTITY (1, 1) NOT NULL ,
    [ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_ChangeDate] DEFAULT (getdate()),
    [RowGUID] [uniqueidentifier] NOT NULL ,
    [ChangeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OldValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NewValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AppName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [UserGUID] [uniqueidentifier] NULL ,
    [TagGUID] [uniqueidentifier] NULL ,
    [Tag] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

Trigger to log inserts

CREATE TRIGGER LogInsert_Nodes ON dbo.Nodes
FOR INSERT
AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /*We dont' log individual field changes Old/New because the row is new.
    So we only have one record - INSERTED*/

    INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)

    SELECT
        getdate(), --ChangeDate
        i.NodeGUID, --RowGUID
        'INSERTED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        '', --FieldName
        i.ParentNodeGUID, --TagGUID
        i.Caption, --Tag
        null, --OldValue
        null --NewValue
    FROM Inserted i

Trigger to log Updates

CREATE TRIGGER LogUpdate_Nodes ON dbo.Nodes
FOR UPDATE AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /* ParentNodeGUID uniqueidentifier */
    IF UPDATE (ParentNodeGUID)
    BEGIN
        INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)
        SELECT 
            getdate(), --ChangeDate
            i.NodeGUID, --RowGUID
            'UPDATED', --ChangeType
            USER_NAME(), HOST_NAME(), APP_NAME(), 
            @SavedUserGUID, --UserGUID
            'Nodes', --TableName
            'ParentNodeGUID', --FieldName
            i.ParentNodeGUID, --TagGUID
            i.Caption, --Tag
            d.ParentNodeGUID, --OldValue
            i.ParentNodeGUID --NewValue
        FROM Inserted i
            INNER JOIN Deleted d
            ON i.NodeGUID = d.NodeGUID
        WHERE (d.ParentNodeGUID IS NULL AND i.ParentNodeGUID IS NOT NULL)
        OR (d.ParentNodeGUID IS NOT NULL AND i.ParentNodeGUID IS NULL)
        OR (d.ParentNodeGUID <> i.ParentNodeGUID)
    END

    /* Caption varchar(255) */
    IF UPDATE (Caption)
    BEGIN
        INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)
        SELECT 
            getdate(), --ChangeDate
            i.NodeGUID, --RowGUID
            'UPDATED', --ChangeType
            USER_NAME(), HOST_NAME(), APP_NAME(), 
            @SavedUserGUID, --UserGUID
            'Nodes', --TableName
            'Caption', --FieldName
            i.ParentNodeGUID, --TagGUID
            i.Caption, --Tag
            d.Caption, --OldValue
            i.Caption --NewValue
        FROM Inserted i
            INNER JOIN Deleted d
            ON i.NodeGUID = d.NodeGUID
        WHERE (d.Caption IS NULL AND i.Caption IS NOT NULL)
        OR (d.Caption IS NOT NULL AND i.Caption IS NULL)
        OR (d.Caption <> i.Caption)
    END

...

/* ImageGUID uniqueidentifier */
IF UPDATE (ImageGUID)
BEGIN
    INSERT INTO AuditLog(
        ChangeDate, RowGUID, ChangeType, 
        Username, HostName, AppName,
        UserGUID, 
        TableName, FieldName, 
        TagGUID, Tag, 
        OldValue, NewValue)
    SELECT 
        getdate(), --ChangeDate
        i.NodeGUID, --RowGUID
        'UPDATED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        'ImageGUID', --FieldName
        i.ParentNodeGUID, --TagGUID
        i.Caption, --Tag
        (SELECT Caption FROM Nodes WHERE NodeGUID = d.ImageGUID), --OldValue
        (SELECT Caption FROM Nodes WHERE NodeGUID = i.ImageGUID) --New Value
    FROM Inserted i
        INNER JOIN Deleted d
        ON i.NodeGUID = d.NodeGUID
    WHERE (d.ImageGUID IS NULL AND i.ImageGUID IS NOT NULL)
    OR (d.ImageGUID IS NOT NULL AND i.ImageGUID IS NULL)
    OR (d.ImageGUID <> i.ImageGUID)
END

Trigger to log Delete

CREATE TRIGGER LogDelete_Nodes ON dbo.Nodes
FOR DELETE
AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /*We dont' log individual field changes Old/New because the row is new.
    So we only have one record - DELETED*/

    INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue,NewValue)

    SELECT
        getdate(), --ChangeDate
        d.NodeGUID, --RowGUID
        'DELETED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        '', --FieldName
        d.ParentNodeGUID, --TagGUID
        d.Caption, --Tag
        null, --OldValue
        null --NewValue
    FROM Deleted d

And in order to know which user in the software did the update, every connection “logs itself onto SQL Server” by calling a stored procedure:

CREATE PROCEDURE dbo.SaveContextUserGUID @UserGUID uniqueidentifier AS

/* Saves the given UserGUID as the session's "Context Information" */
IF @UserGUID IS NULL
BEGIN
    PRINT 'Emptying CONTEXT_INFO because of null @UserGUID'
    DECLARE @BinVar varbinary(128)
    SET @BinVar = CAST( REPLICATE( 0x00, 128 ) AS varbinary(128) )
    SET CONTEXT_INFO @BinVar
    RETURN 0
END

DECLARE @UserGUIDBinary binary(16) --a guid is 16 bytes
SELECT @UserGUIDBinary = CAST(@UserGUID as binary(16))
SET CONTEXT_INFO @UserGUIDBinary


/* To load the guid back 
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

select @SavedUserGUID AS UserGUID
*/

Notes

  • Stackoverflow code format removes most blank lines – so formatting sucks
  • We use a table of users, not integrated security
  • This code is provided as a convience – no critisism of our design selection allowed. Purists might insist that all logging code should be done in the business layer – they can come here and write/maintain it for us.
  • blobs cannot be logged using triggers in SQL Server (there is no “before” version of a blob – there is only what is). Text and nText are blobs – which makes notes either unloggable, or makes them varchar(2000)’s.
  • the Tag column is used as an arbitrary text to identify the row (e.g. if a customer was deleted, the tag will show “General Motors North America” in the audit log table.
  • TagGUID is used to point to the row’s “parent”. For example logging InvoiceLineItems points back to the InvoiceHeader. This way anyone searching for audit log entries related for a specific invoice will find the deleted “line items” by the line item’s TagGUID in the audit trail.

  • sometimes the “OldValue” and “NewValue” values are written as a sub-select – to get a meaningful string. i.e.”

    OldValue: {233d-ad34234..}
    NewValue: {883-sdf34…}

is less useful in the audit trail than:

OldValue: Daimler Chrysler
NewValue: Cerberus Capital Management

Final note: Feel free to not do what we do. This is great for us, but everyone else is free to not use it.

More Related Contents:

  1. How to find a text inside SQL Server procedures / triggers?
  2. T-SQL stored procedure that accepts multiple Id values
  3. Select columns from result set of stored procedure
  4. Escape a string in SQL Server so that it is safe to use in LIKE expression
  5. Insert Update trigger how to determine if insert or update
  6. Retrieve column definition for stored procedure result set
  7. Dapper.NET and stored proc with multiple result sets
  8. How to list all dates between two dates [duplicate]
  9. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  10. EXEC sp_executesql with multiple parameters
  11. Creating audit triggers in SQL Server
  12. T-SQL Dynamic SQL and Temp Tables
  13. How do I search an SQL Server database for a string?
  14. Calling an API from SQL Server stored procedure
  15. How to: Create trigger for auto update modified date with SQL Server 2008
  16. error when inserting into table having instead of trigger from entity data framework
  17. Handling date in SQL Server
  18. Retrieve data from stored procedure which has multiple result sets
  19. Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
  20. Calling stored procedure using VBA
  21. Stored Procedure and Permissions – Is EXECUTE enough?
  22. Avoid Naming User Stored Procedures SP% or SP_%
  23. Execute stored procedure with an Output parameter?
  24. Stored procedure – return identity as output parameter or scalar
  25. Trigger to fire only if a condition is met in SQL Server
  26. Python – pyodbc call stored procedure with parameter name
  27. SQL Server stored procedure return code oddity
  28. How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?
  29. ODBC Call Failed with stored procedure – Pass through query
  30. Why can’t Entity Framework see my Stored Procedure’s column information?

Leave a Comment