SQL History table and triggers

Question

I have a requirement to keep a history of changes made to a specific table when there is an UPDATE called, but only care about specific columns.

So, I have created a History table:

CREATE TABLE [dbo].[SourceTable_History](
    [SourceTable_HistoryID] [int] IDENTITY(1,1) NOT NULL,
    [SourceTableID] [int] NOT NULL,
    [EventDate] [date] NOT NULL,
    [EventUser] [date] NOT NULL,
    [ChangedColumn] VARCHAR(50) NOT NULL,
    [PreviousValue] VARCHAR(100) NULL,
    [NewValue] VARCHAR(100) NULL

    CONSTRAINT pk_SourceTable_History PRIMARY KEY ([SourceTable_HistoryID]),
    CONSTRAINT fk_SourceTable_HistoryID_History_Source FOREIGN KEY ([SourceTableID]) REFERENCES SourceTable (SourceTableId)
)

Abd my plan is to create an Update trigger on the SourceTable. The business only cares about changes to certain columns, so, in psudo code, I was planning to do something like

If source.Start <> new.start
Insert into history (PrimaryKey, EventDate, EventUser, ColumnName, OldValue, NewValue)
(PK, GETDATYE(), updateuser, "StartDate", old.value, new.value)

And there would be a block like that per column we want history on.

We're NOT allowed to use CDC, so we have to roll our own, and this is my plan so far.

Does this seem a suitable plan?

There are 7 tables we need to monitor, with a column count of between 2 and 5 columns per table.

I just need to work out how to get a trigger to first comapr the before and after values of a specific columnm and then write a new row.

I thought it was something as simple as:

CREATE TRIGGER tr_PersonInCareSupportNeeds_History
ON PersonInCareSupportNeeds
FOR UPDATE
AS
BEGIN
    IF(inserted.StartDate <> deleted.StartDate)
    BEGIN
        INSERT INTO [dbo].[PersonInCareSupportNeeds_History]
        ([PersonInCareSupportNeedsID], [EventDate], [EventUser], [ChangedColumn], [PreviousValue], [NewValue])
        VALUES
        (inserted.[PersonInCareSupportNeedsID], GETDATE(), [LastUpdateUser], 'StartDate', deleted.[StartDate], deleted.[StartDate])
    END
END

Answer 1

We have trigger based auditing system and we basically created it by analyzing how third party tool for generating audit triggers ApexSQL Audit creates triggers and manages storage and developed our own system based on that.

I think your solution is generally ok but that you need to think about modifying storage a bit and plan for scaling.

What if business decides to keep track of all columns in all tables? What if they decide to track inserts and deletes as well? Will your solution be able to accommodate this?

Storage: Use two tables to hold your data. One table for holding all info about transactions (when, who, application name, table name, schema name, affected rows, etc… ). And another table to hold the actual data (before and after values, primary key, etc..).

Triggers: We ended up with a template for insert, update and delete triggers and very simple C# app where we enter tables and columns so application outputs DDL. This saved us a lot of time.

Answer 2

Well I think that your idea is not so bad. Actually, I have the similar system in production. I will not give you my complete code (with acynchronious history saving), but I could give you some guidelines.

The main idea is to turn your data from relational model into Entity-Attribute-Value model. Also we want our triggers to be as much general as we can, that means - do not write column names explicitly. This could be done by different ways, but the most general I know in SQL Server is to use FOR XML and then select from xml:

declare @Data xml

select @Data = (select * from Test for xml raw('Data'))

select
    T.C.value('../@ID', 'bigint') as ID,
    T.C.value('local-name(.)', 'nvarchar(128)') as Name,
    T.C.value('.', 'nvarchar(max)') as Value
from @Data.nodes('Data/@*') as T(C)

SQL FIDDLE EXAMPLE

To get different rows of two tables, you could use EXCEPT:

select * from Test1 except select * from Test2
union all
select * from Test2 except select * from Test1

SQL FIDDLE EXAMPLE

and, finally, your trigger could be something like this:

create trigger utr_Test_History on Test
after update
as
begin
    declare @Data_Inserted xml, @Data_Deleted xml

    select @Data_Inserted = 
    (
        select * 
        from (select * from inserted except select * from deleted) as a
        for xml raw('Data')
    )

    select @Data_Deleted = 
    (
        select * 
        from (select * from deleted except select * from inserted) as a
        for xml raw('Data')
    )

    ;with CTE_Inserted as (
        select
            T.C.value('../@ID', 'bigint') as ID,
            T.C.value('local-name(.)', 'nvarchar(128)') as Name,
            T.C.value('.', 'nvarchar(max)') as Value
        from @Data_Inserted.nodes('Data/@*') as T(C)    
    ), CTE_Deleted as (
        select
            T.C.value('../@ID', 'bigint') as ID,
            T.C.value('local-name(.)', 'nvarchar(128)') as Name,
            T.C.value('.', 'nvarchar(max)') as Value
        from @Data_Deleted.nodes('Data/@*') as T(C)     
    )
    insert into History (Table_Name, Record_ID, Event_Date, Event_User, Column_Name, Value_Old, Value_New)
    select 'Test', isnull(I.ID, D.ID), getdate(), system_user, isnull(D.Name, I.Name), D.Value, I.Value
    from CTE_Inserted as I
        full outer join CTE_Deleted as D on D.ID = I.ID and D.Name = I.Name
    where
    not
    (
        I.Value is null and D.Value is null or
        I.Value is not null and D.Value is not null and I.Value = D.Value
    )
end

SQL FIDDLE EXAMPLE

Answer 3

Depending on your requirments, I think history tables should mirror the table you want to capture, plus the extra audit details (who, when, why).

That can make it easier to use the same existing logic (sql, data classes, screens etc) to view historical data.

With your design getting the data in will be ok, but how easy will it be to pull the data out in a usable format?