SQL History Table and Triggers

Question

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

So, I created the 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 in SourceTable. Business only cares about changes in certain columns, so in the psudo code I planned 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 will be a block similar to this column in which we want to include the story.

We are NOT allowed to use CDC, so we have to minimize it ourselves, and this is my plan.

Does this sound like a suitable plan?

There are 7 tables that we need to track, with the number of columns from 2 to 5 columns per table.

I just need to figure out how to get the trigger for the first comapr before and after the values ​​of a specific column, and then write a new row.

I thought it was simple:

 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 an audit system based on triggers, and we basically created it by analyzing it as a third-party tool for creating audit triggers. Audit ApexSQL creates triggers and manages the storage and based on this develops our own system.

I think your solution is generally fine, but you need to think a little about changing the repository and plan for scaling.

What if the business decides to track all columns in all tables? What if they decide to also track insertions and deletions? Can you solve this decision?

Storage: Use two tables to store your data. One table to store all transaction information (when, who, application name, table name, schema name, affected rows, etc.). And another table for storing the actual data (before and after the values, primary key, etc.).

Triggers: We ended up with a template for insert, update and delete triggers and a very simple C # application where we introduce tables and columns, so the applications exit DDL. It saved us a lot of time.

Answer 2

Depending on your requirements, I think the history tables should reflect the table you want to capture, as well as additional audit data (who, when, why).

This makes it easier to use the same existing logic (sql, data classes, screens, etc.) to view historical data.

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

Answer 3

Well, I think your idea is not so bad. In fact, I have a similar system in production. I won’t give you my full code (preserving an asynchronous history), but I could give you some recommendations.

The basic idea is to turn your data from a relational model into a value attribute-object model . In addition, we want our triggers to be as general as we can, which means you are not writing column names explicitly. There are many ways to do this, but the most common thing 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 TCvalue('../@ID', 'bigint') as ID, TCvalue('local-name(.)', 'nvarchar(128)') as Name, TCvalue('.', 'nvarchar(max)') as Value from @Data.nodes('Data/@*') as T(C) 

FIDDLE SQL Example

To get different rows from two tables, you can 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 might 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 TCvalue('../@ID', 'bigint') as ID, TCvalue('local-name(.)', 'nvarchar(128)') as Name, TCvalue('.', 'nvarchar(max)') as Value from @Data_Inserted.nodes('Data/@*') as T(C) ), CTE_Deleted as ( select TCvalue('../@ID', 'bigint') as ID, TCvalue('local-name(.)', 'nvarchar(128)') as Name, TCvalue('.', '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 

FIDDLE SQL Example