SQL Server history table - populate through SP or Trigger?

Question

In my SQL Server backend for my app, I want to create history tables for a bunch of my key tables, which will track a history of changes to the rows.

My entire application uses Stored Procedures, there is no embedded SQL. The only connection to the database to modify these tables will be through the application and the SP interface. Traditionally, shops I've worked with have performed this task using triggers.

If I have a choice between Stored Procedures and Triggers, which is better? Which is faster?

Answer 1

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.

Answer 2

in SQL server 2008 a new feature called CDC (Change data Capture) CDC on MSDN can help. CDC is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server thus making the details of the changes available in relational format.

Channel9 video

Answer 3

We have a third party tool ApexSQL Audit that we used to generate triggers.

Here is how triggers look like in the background and how data is stored. Hopefully guys will find this useful enough to reverse engineer the process. Its a bit different from what Ian Boyd showed in his examples because it allows each columns to be audited separately.

Table 1 – holds transaction details (who, when, application, host name, etc)

CREATE TABLE [dbo].[AUDIT_LOG_TRANSACTIONS](
    [AUDIT_LOG_TRANSACTION_ID] [int] IDENTITY(1,1) NOT NULL,
    [DATABASE] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](261) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](261) NOT NULL,
    [AUDIT_ACTION_ID] [tinyint] NOT NULL,
    [HOST_NAME] [varchar](128) NOT NULL,
    [APP_NAME] [varchar](128) NOT NULL,
    [MODIFIED_BY] [varchar](128) NOT NULL,
    [MODIFIED_DATE] [datetime] NOT NULL,
    [AFFECTED_ROWS] [int] NOT NULL,
    [SYSOBJ_ID]  AS (object_id([TABLE_NAME])),
  PRIMARY KEY CLUSTERED 
  (
       [AUDIT_LOG_TRANSACTION_ID] ASC
  )
)

Table 2 – holds before/after values.

CREATE TABLE [dbo].[AUDIT_LOG_DATA](
   [AUDIT_LOG_DATA_ID] [int] IDENTITY(1,1) NOT NULL,
   [AUDIT_LOG_TRANSACTION_ID] [int] NOT NULL,
   [PRIMARY_KEY_DATA] [nvarchar](1500) NOT NULL,
   [COL_NAME] [nvarchar](128) NOT NULL,
   [OLD_VALUE_LONG] [ntext] NULL,
   [NEW_VALUE_LONG] [ntext] NULL,
   [NEW_VALUE_BLOB] [image] NULL,
   [NEW_VALUE]  AS (isnull(CONVERT([varchar](8000),      [NEW_VALUE_LONG],0),CONVERT([varchar](8000),CONVERT([varbinary](8000),substring([NEW_VALUE_BLOB],(1),(8000)),0),0))),
   [OLD_VALUE]  AS (CONVERT([varchar](8000),[OLD_VALUE_LONG],0)),
   [PRIMARY_KEY]  AS ([PRIMARY_KEY_DATA]),
   [DATA_TYPE] [char](1) NOT NULL,
   [KEY1] [nvarchar](500) NULL,
   [KEY2] [nvarchar](500) NULL,
   [KEY3] [nvarchar](500) NULL,
   [KEY4] [nvarchar](500) NULL,
PRIMARY KEY CLUSTERED 
 (
    [AUDIT_LOG_DATA_ID] ASC
)
)

Insert trigger

I’m not showing triggers for update because they are quite long and have the same logic as this one.

CREATE TRIGGER [dbo].[tr_i_AUDIT_Audited_Table]
ON [dbo].[Audited_Table]
FOR INSERT
NOT FOR REPLICATION
As
BEGIN
DECLARE 
    @IDENTITY_SAVE              varchar(50),
    @AUDIT_LOG_TRANSACTION_ID       Int,
    @PRIM_KEY               nvarchar(4000),
    @ROWS_COUNT             int

SET NOCOUNT ON
Select @ROWS_COUNT=count(*) from inserted
Set @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

INSERT
INTO dbo.AUDIT_LOG_TRANSACTIONS
(
    TABLE_NAME,
    TABLE_SCHEMA,
    AUDIT_ACTION_ID,
    HOST_NAME,
    APP_NAME,
    MODIFIED_BY,
    MODIFIED_DATE,
    AFFECTED_ROWS,
    [DATABASE]
)
values(
    'Audited_Table',
    'dbo',
    2,  --  ACTION ID For INSERT
    CASE 
      WHEN LEN(HOST_NAME()) < 1 THEN ' '
      ELSE HOST_NAME()
    END,
    CASE 
      WHEN LEN(APP_NAME()) < 1 THEN ' '
      ELSE APP_NAME()
    END,
    SUSER_SNAME(),
    GETDATE(),
    @ROWS_COUNT,
    'Database_Name'
)

Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()    

--This INSERT INTO code is repeated for each columns that is audited. 
--Below are examples for only two columns
INSERT INTO dbo.AUDIT_LOG_DATA
(
    AUDIT_LOG_TRANSACTION_ID,
    PRIMARY_KEY_DATA,
    COL_NAME,
    NEW_VALUE_LONG,
    DATA_TYPE
    , KEY1
)
SELECT
    @AUDIT_LOG_TRANSACTION_ID,
    convert(nvarchar(1500), IsNull('[PK_Column]='+CONVERT(nvarchar(4000), NEW.[PK_Column], 0), '[PK_Column] Is Null')),
    'Column1',
    CONVERT(nvarchar(4000), NEW.[Column1], 0),
    'A'
    , CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[PK_Column], 0))
FROM inserted NEW
WHERE NEW.[Column1] Is Not Null

 --value is inserted for each column that is selected for auditin
INSERT INTO dbo.AUDIT_LOG_DATA
(
    AUDIT_LOG_TRANSACTION_ID,
    PRIMARY_KEY_DATA,
    COL_NAME,
    NEW_VALUE_LONG,
    DATA_TYPE
    , KEY1
)
SELECT
    @AUDIT_LOG_TRANSACTION_ID,
    convert(nvarchar(1500), IsNull('[PK_Column]='+CONVERT(nvarchar(4000), NEW.[PK_Column], 0), '[PK_Column] Is Null')),
    'Column2',
    CONVERT(nvarchar(4000), NEW.[Column2], 0),
    'A'
    , CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[PK_Column], 0))
    FROM inserted NEW
    WHERE NEW.[Column2] Is Not Null
End

Disclaimer: I’m not affiliated with Apex in any way but I do use their tools in my current job.

Answer 4

As everyone else said, Triggers. They are easier to unit test and far more resilient to power users with unexpected access directly to the tables making random queries.

As for faster? Determining what is fast inside a database is a hard problem with large number of variables. Short of "try it both ways and compare" you are not going to get a useful answer to which method is faster. The variables include the size of the tables involved, the normal pattern of updates, the speed of the disks in the server, the amount of memory, the amount of memory devoted to caching, etc. This list is endless and each variable affects whether triggers are faster than custom SQL inside the SP.

Good. Fast. Cheap. Pick two. Triggers are Good in terms of integrity and probably Cheap in terms of maintenance. Arguably they are also Fast in that once they work, you are done with them. SPs are a maintenance issue and pushing stuff into maintenance can be Fast, but is never Good or Cheap.

Good Luck.

Answer 5

Recommended approach depends on your requirements. If the history table is there for audit trail, you need to capture each operation. If history table is only for performance reasons, then a scheduled SQL Agent data transfer job should be enough.

For capturing each operation use either AFTER TRIGGERs or Change Data Capture.

After triggers provide you with two temp tables to operate with inside the trigger:

• INSERTED after INSERT or UPDATE
• DELETED after DELETE

You can perform inserts to the history table from these temp tables and your history table will always be up-to-date. You might want to add version numbering, time stamps or both in the history table to separate changes to a single source row.

Change Data Capture (CDC) is designed for creating a delta table that you can use as a source for loading data into a data warehouse (or a history table). Unlike triggers, CDC is asynchronous and you can use any method and scheduling for populating your destination (sprocs, SSIS).

You can access both original data and changes with CDC. Change Tracking (CT) only detects changed rows. It is possible to construct a complete audit trail with CDC but not with CT. CDC and CT are both only available in the MSSQL 2008 Enterprise and Developer Editions.

Answer 6

Use triggers for this. This means that any changes, regardless of source, will be reflected in the history table. It's good for security, resilient to failure modes like people forgetting to add code to update the history table and so forth.

There is not likely to be any particular speed difference in either for this type of operation as execution time will be dominated by the I/O.

Answer 7

One issue to be very careful about is to identify your intended use cases for this table, and make sure it's constructed properly for that purpose.

Specifically, if it's for an operational audit trail for stakeholders, that's quite different from before-and-after snapshots of record changes in tables. (In fact, I have a difficult time imagining a good use for record changes, other than debugging.)

An audit trail normally requires, at minimum, a user id, a timestamp, and an operation code - and probably some detail about the operation. Example - change the ordered quantity on a line item on a purchase order.

And for this type of audit trail you do not want to use triggers. The higher in the BR layer you embed the generation of these events, the better.

OTOH, for record-level changes, triggers are the right match. But it's also often easier to get this from your dbms journaling files.

Answer 8

I prefer to use triggers for audit tables because triggers can capture all updates, inserts and deletes, not just the updates, inserts and deletes invoked through certain stored procedures:

CREATE TRIGGER [dbo].[tr_Employee_rev]
ON [dbo].[Employee]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
    IF EXISTS(SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
    BEGIN
        INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT inserted.ID, inserted.Firstname,inserted.Initial,inserted.Surname,inserted.Birthdate,'u', GetDate(), SYSTEM_USER FROM INSERTED
    END 

    IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
    BEGIN
        INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT inserted.ID, inserted.Firstname,inserted.Initial,inserted.Surname,inserted.Birthdate,'i', GetDate(), SYSTEM_USER FROM INSERTED
    END

    IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
    BEGIN
        INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT deleted.ID, deleted.Firstname,deleted.Initial,deleted.Surname,deleted.Birthdate,'d', GetDate(), SYSTEM_USER FROM DELETED 
    END
END

I use SQLServer to generate the SQL for the revision tables instead of hand coding it. This code is available on https://github.com/newdigate/sqlserver-revision-tables

Answer 9

Triggers. Right now you might be able to say that the only way data is updated is through your SPs, but things can change or you might need to do a mass insert/update that using the SPs will be too cumbersome for. Go with triggers.

Answer 10

It depends on the nature of the application and the table structure, number of indexes, data size, etc, foreign keys, etc. If these are relatively simple tables (no or few indexes like indexes on datetime/integer columns) with a limited data set (< 1 Million rows), you will probably be ok to use triggers.

Keep in mind that triggers can be the source of locking issues. I would assume that if your are using the history tables as a type of audit trail you will be indexing them for future reference. If the trigger updates the history table which is slow to insert/update/delete due to the indexes, the procedure call will block until the trigger finishes. Also, if there are any foreign key constraints that will be updated in the trigger, this could also hamper performance.

In this case it all depends on the table indexes. We use Sql Server 2000 for a 24/7 app that processes over 100K financial transactions per day. The largest/main table has over 100Million rows and 15 indexes (mass deletes are not reasonably possible if uptime is desired). Even though all SQL is done in Stored Procedures, we do not use triggers or foreign keys because of the performance hit.

Answer 11

Triggers. Here is my approach:

1. Create one audit table for each critical table that requires audit trial
2. Audit table will include all columns from source table + columns audit record info such as who, when and the action
3. Trigger for UPDATE and DELETE only, the INSERT operation will have the pristine record in source table itself
4. Before update or delete, copy the original record + audit info to audit table
5. (Optionally - for UPDATE only:) To know which column got updated, use either UPDATE(ColumnName) or COLUMNS_UPDATED() built in SQL function to determine the affected columns

Auditing this way keeps the current status in the source table and all the history in audit table and easily identified by the key columns.