All geek questions in one place

The best solution for creating change history for multiple SQL tables

I searched for a long time and have not yet found a solution. Also this question is a bit subjective. But I really need a good solution for this task.

I have a table with volumes, security and more. This data changes daily. Now I need to save these changes in history .

What is the best solution for this? Creating a second table with the same structure and just pasting the "old" data into this table? Or just add an extra parameter with status?

Creating this server side using triggers? Or programmatically using the LINQ procedure?

+6
source share
5 answers

We have selected individual tables, as this will support your main table faster and faster. We also selected triggers, believing that if you ever change the input mechanism, you will not want to rewrite your audit. It can also record random changes away from the DBA.

Since updating is actually deleting and then pasting, you can achieve what was suggested with a single trigger - this is what we did.

  • Create a table that exactly matches your existing table, but with some columns added: AUDIT_GUID VARCHAR (40), AUDIT_TIMESTAMP DATETIME, AUDIT_ACTION VARCHAR (20)

  • Create a “AFTER INSERT, DELETE, UPDATE” trigger using the following general template (just add more columns if necessary).

    CREATE TRIGGER CustomerAudit ON Customer AFTER INSERT,DELETE,UPDATE AS BEGIN IF (TRIGGER_NESTLEVEL()>1) RETURN DECLARE @Time DateTime = CURRENT_TIMESTAMP DECLARE @Audit_GUID varchar(100) = NEWID() INSERT INTO Customer_History (FirstName, LastName, Audit_Date, Audit_Action, Audit_GUID) SELECT FirstName, LastName, @Time, 'Delete', @Audit_GUID FROM Deleted INSERT INTO Customer_History (FirstName, LastName, Audit_Date, Audit_Action, Audit_GUID) SELECT FirstName, LastName, @Time, 'Insert', @Audit_GUID FROM Inserted END

If you want to find updates, they will be rows in the history table that have deletion and update with the same Audit_GUID value. The timestamp also allows you to check for changes made at a specific time, and we also added currentuser to find the culprit in this case!

+5
source

I would choose the ON DELETE / ON UPDATE trigger, which stores deleted or modified rows in a second table.

So you

  • can maintain referential integrity
  • can create reasonable unique keys in the "live data" table
  • no need to remember adding WHERE IsDeleted = 'N' (or the like) with every request you make.
  • have automatic historization with your data, even if you modify it directly in the database

It provides convenience and performance with your live data. For historical data, you will need to go to your history tables, which in my experience can be cumbersome if the queries are complex and you want to mix live and historical data into one result. Depending on your use case, if such a use is a likely scenario.

+2
source

I think it’s better to create a separate history tracking table along with the required fields and dates so that you can query this table as needed. You can use triggers to register.

+1
source

I suggest creating a historical table (suffix _HIST) with all the data. You need three triggers to hold UPDATES, DELETIONS, and INSERTIONS.

0
source

Do not build what you cannot buy from the shelf. Google for database validation software, there are many.

0
source

Source: https://habr.com/ru/post/888013/

More articles:


All Articles