Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)

Views: 35.064 views
This post is the 12 part of 20 in the series. Security and Audit
Reading Time: 6 minutes

Hello people,
Good Morning!

In today's post, I will demonstrate how to create a history of data changes, whether by INSERT, DELETE, or UPDATE in a table, so that you can know which user and when information was changed and what was the information of the time. This is very useful for auditing or BI reporting that needs to get the scenario of the time, such as the customer salary at the time he purchased a product, 5 years ago.

For this operation, I will create a table “clone” of the original table, but with an update date, the information of the user who made the change, hostname, operation performed and I will create a trigger on the table, so that at each DML operation record a record in the history table.

Create history using Trigger or System?

Before presenting this solution, I would first like to demonstrate some views on creating table triggers for this purpose. For a long time, I was totally against creating database triggers, thinking that this would make them less performative, a very large update or delete could generate production locks, etc. and thought the developer who should be concerned , via system source code, with this part of logs and not creating triggers in the database.

That changed when I began to look at the programmer side and think about information traceability, and how much it can help other sectors of a company, such as Auditing, BI, and Controlling. Nowadays, with the introduction of the DevOPS philosophy, DBA can't think inside the black box, focusing only on database availability. We need to focus on the business and add as much value as IT can to the business, but of course, always on the basis of what is possible, without compromising the availability of IT resources.

Given this, I will list the differences between creating this data history using trigger or via system:

Generating history by triggering the database:

  • Once deployed, deploying the feature only involves creating a table and triggering the database.
  • No matter which routine or user is manipulating the table, all changes will always be written.
  • UPDATE, INSERT and DELETE done manually in the database will be logged and audited by the trigger, and history will be generated for it.
  • Both DBA and Developer have visibility into the existence of the routine and its source code.
  • If it is necessary to temporarily disable triggering for some operation, this can be done within seconds by DBA
  • Managing the audit routine is in the hands of the DBA.
  • If the table undergoes a large manual data change, whether via INSERT, DELETE, or UPDATE, all changes will be written to the history table, which can lead to a large volume of history table writes and slow down the environment. This can be circumvented by disabling the trigger while these bulk changes are made and activating again at the end.
  • If the change is made by the system, and the system uses a fixed user, the trigger will record the system user, not the user of the person who made the change.

Generating history through the system:

  • Implementation involves making changes to the source code of all application code snippets and screens that manipulate data in the involved table (in addition to dependent files), where there are often rigid windows for any system modifications.
  • Only screens that have been changed to record history will actually do so.
  • UPDATE, INSERT, and DELETE done manually in the database will NOT be logged and there will be no history for these changes.
  • Only the developer knows that this feature exists and how it works. The DBA usually does not have access to this kind of information let alone the source code to understand how this history is being generated.
  • If it is necessary to temporarily disable this feature, the developer will have to change the application source code and deploy it to production, consuming a lot of time from two teams and being able to disconnect active sessions on the application server.
  • Audit routine management is in the hands of the Developer
  • If the table undergoes a large manual data change, either via INSERT, DELETE or UPDATE, the environment will not be affected as manual changes to the database will not be recorded.
  • If the change is made by the system, it is possible to identify the user logged in the application and record the login or even perform queries in the database and return a User_ID from the table Users, for example, to write to the history table.

As you noted in the items cited above, there are advantages and disadvantages to each approach. Therefore, you must decide which one best fits your business and infrastructure.

Creating the audit process

For the trigger tests, I will create a customer table and enter some data to visualize the log working:

SQL Server - Trigger audit log data change history

Finally, the time has come to create our audit:

And now let's simulate some changes in the base:

SQL Server - Trigger audit log data change history 2

As you can see, I am using the Inserted object in the part that deals with UPDATE. With this, I write to the history table the new information, which is being updated by the UPDATE command. If you want to save the old values ​​that are being overwritten, just change the trigger to use the Deleted object.

Changes to the original table

If you need to include more columns in your original table, remember to also change the history table of your trigger, since I used * to enter the data just to not have to type the name of the columns. An alternative to this is to remove the * from the trigger and manually specify the name of all columns, which has no problem there.

Simulating that you change your original table without changing the history table, we have this situation:
SQL Server - Trigger audit log data change history error

Where the error message was generated:

Msg 8101, Level 16, State 1, Procedure trgHistoric_Client, Line 17
An explicit value for the identity column in table 'Tests.dbo.Cliente_Log' can only be specified when a column list is used and IDENTITY_INSERT is ON.

To fix this, simply add the same structure change made to the log table:
SQL Server - Trigger audit log data change history error 2

If you want to identify the query that triggered the trigger, read the article SQL Server - How to identify the query that triggered a DML trigger on a table.

And that's it folks!
Thanks for visiting and if you have any questions, leave it here in the comments.