17 July 2016
14
Monitoring Query Development Audit Database SQL Server

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

Views: 35.064 views

In this article

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:

IF (OBJECT_ID('Testes.dbo.Cliente') IS NOT NULL) DROP TABLE Testes.dbo.Cliente
CREATE TABLE Testes.dbo.Cliente (
    Id_Cliente INT IDENTITY(1, 1),
    Nome VARCHAR(100),
    Data_Nascimento DATETIME,
    Salario FLOAT
)

INSERT INTO Testes.dbo.Cliente
VALUES 
    ('João', '1981-05-14', 4521),
    ('Marcos', '1975-01-07', 1478.58),
    ('André', '1962-11-11', 7151.45),
    ('Simão', '1991-12-18', 2584.97),
    ('Pedro', '1986-11-20', 987.52),
    ('Paulo', '1974-08-04', 6259.14),
    ('José', '1979-09-01', 5272.13)


SELECT * FROM Testes.dbo.Cliente

IF (OBJECT_ID('Testes.dbo.Cliente') IS NOT NULL) DROP TABLE Testes.dbo.Cliente

CREATE TABLE Testes.dbo.Cliente (

Id_Cliente INT IDENTITY(1, 1),

Nome VARCHAR(100),

Data_Nascimento DATETIME,

Salario FLOAT

)

INSERT INTO Testes.dbo.Cliente

VALUES

('João', '1981-05-14', 4521),

('Marcos', '1975-01-07', 1478.58),

('André', '1962-11-11', 7151.45),

('Simão', '1991-12-18', 2584.97),

('Pedro', '1986-11-20', 987.52),

('Paulo', '1974-08-04', 6259.14),

('José', '1979-09-01', 5272.13)

SELECT * FROM Testes.dbo.Cliente

Finally, the time has come to create our audit:

-- Criando a tabela com a mesma estrutura da original, mas adicionando colunas de controle
IF (OBJECT_ID('Testes.dbo.Cliente_Log') IS NOT NULL) DROP TABLE Testes.dbo.Cliente_Log
CREATE TABLE Testes.dbo.Cliente_Log (
    Id INT IDENTITY(1, 1),
    Dt_Atualizacao DATETIME DEFAULT GETDATE(),
    [Login] VARCHAR(100),
    Hostname VARCHAR(100),
    Operacao VARCHAR(20),

    -- Dados da tabela original
    Id_Cliente INT,
    Nome VARCHAR(100),
    Data_Nascimento DATETIME,
    Salario FLOAT
)
GO

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgHistorico_Cliente' AND parent_id = OBJECT_ID('Testes.dbo.Cliente')) > 0) DROP TRIGGER trgHistorico_Cliente
GO

CREATE TRIGGER trgHistorico_Cliente ON Testes.dbo.Cliente -- Tabela que a trigger será associada
AFTER INSERT, UPDATE, DELETE AS
BEGIN
    
    SET NOCOUNT ON

    DECLARE 
        @Login VARCHAR(100) = SYSTEM_USER, 
        @HostName VARCHAR(100) = HOST_NAME(),
        @Data DATETIME = GETDATE()
        

    IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))
    BEGIN
        
        INSERT INTO Testes.dbo.Cliente_Log
        SELECT @Data, @Login, @HostName, 'UPDATE', *
        FROM Inserted

    END
    ELSE BEGIN

        IF (EXISTS(SELECT * FROM Inserted))
        BEGIN

            INSERT INTO Testes.dbo.Cliente_Log
            SELECT @Data, @Login, @HostName, 'INSERT', *
            FROM Inserted

        END
        ELSE BEGIN

            INSERT INTO Testes.dbo.Cliente_Log
            SELECT @Data, @Login, @HostName, 'DELETE', *
            FROM Deleted

        END

    END

END
GO

-- Criando a tabela com a mesma estrutura da original, mas adicionando colunas de controle

IF (OBJECT_ID('Testes.dbo.Cliente_Log') IS NOT NULL) DROP TABLE Testes.dbo.Cliente_Log

CREATE TABLE Testes.dbo.Cliente_Log (

Id INT IDENTITY(1, 1),

Dt_Atualizacao DATETIME DEFAULT GETDATE(),

[Login] VARCHAR(100),

Hostname VARCHAR(100),

Operacao VARCHAR(20),

-- Dados da tabela original

Id_Cliente INT,

Nome VARCHAR(100),

Data_Nascimento DATETIME,

Salario FLOAT

)

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgHistorico_Cliente' AND parent_id = OBJECT_ID('Testes.dbo.Cliente')) > 0) DROP TRIGGER trgHistorico_Cliente

CREATE TRIGGER trgHistorico_Cliente ON Testes.dbo.Cliente -- Tabela que a trigger será associada

AFTER INSERT, UPDATE, DELETE AS

BEGIN

SET NOCOUNT ON

DECLARE

@Login VARCHAR(100) = SYSTEM_USER,

@HostName VARCHAR(100) = HOST_NAME(),

@Data DATETIME = GETDATE()

IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))

BEGIN

INSERT INTO Testes.dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'UPDATE', *

FROM Inserted

END

ELSE BEGIN

IF (EXISTS(SELECT * FROM Inserted))

BEGIN

INSERT INTO Testes.dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'INSERT', *

FROM Inserted

END

ELSE BEGIN

INSERT INTO Testes.dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'DELETE', *

FROM Deleted

END

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

INSERT INTO Testes.dbo.Cliente
VALUES ('Bartolomeu', '1975-05-28', 6158.74)

UPDATE Testes.dbo.Cliente
SET Salario = Salario * 1.5
WHERE Nome = 'Bartolomeu'

DELETE FROM Testes.dbo.Cliente
WHERE Nome = 'André'

UPDATE Testes.dbo.Cliente
SET Salario = Salario * 1.1
WHERE Id_Cliente = 2

UPDATE Testes.dbo.Cliente
SET Salario = 10, Nome = 'Judas Iscariodes', Data_Nascimento = '06/06/2066'
WHERE Id_Cliente = 1

INSERT INTO Testes.dbo.Cliente

VALUES ('Bartolomeu', '1975-05-28', 6158.74)

UPDATE Testes.dbo.Cliente

SET Salario = Salario * 1.5

WHERE Nome = 'Bartolomeu'

DELETE FROM Testes.dbo.Cliente

WHERE Nome = 'André'

UPDATE Testes.dbo.Cliente

SET Salario = Salario * 1.1

WHERE Id_Cliente = 2

UPDATE Testes.dbo.Cliente

SET Salario = 10, Nome = 'Judas Iscariodes', Data_Nascimento = '06/06/2066'

WHERE Id_Cliente = 1

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:

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:

Important!
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.

Hug!

Tags: log financial security sql server sql auditing

Eduardo Araujo said:

5 April 2021 06 gies: 29

Hello. If you want to create other fields to store the previous values and also the updated ones, how would you do it? Example:
Name before; Name
Previous_Nascimento_Date; Birth date
Previous_ Salary; Wage

In the case of inclusion, the “Previous fields would be empty”. In the case of the update all would be filled, the ideal would be to fill only those fields that have been updated, and in the Exclusion only the current fields were filled.

Is it possible to create triggers in this way?

Edward.

Reply
Bruno Brazil said:

25 February 2021 to 12: 05

You can save the user in the hot table, replicating to the log.

Reply
Rafael said:

18 January 2021 to 11: 08

Dirceu, okay? Dude that your site is a ball show !! help me a lot. Congratulations !. I implanted this trigger and I really liked the result, an information I needed to change would be the user, instead of presenting the user of the bank, presenting the user of my application, can I adjust this?

Reply
Raphael Christopher said:

24 from 2020 to 19 at 25: XNUMX

To save the information that was replaced in the update, should I change "INSERTED" to "DELETED" after the from in the update clause?

Reply
Júlio Santos said:

11 May 2018 to 16: 13

Great tutorial, congratulations, helped me a lot here.

Reply
Jose Eustaquio Santana said:

5 April 2018 09 gies: 31

Hello, I liked the content!

But I have a doubt!

I use only one user to connect to SQL Server database

And users are written into databases, and used only in the application!

How can I pass this data to the trigger (user data)?

If the whole operation happens in the database, how will I pass the ERP user login ???

Thank you very much in advance!

Reply
Mario Jr said:

13 from 2017 from September to 08: 15

Simple and objective tutorial!

Thank you very much!

Reply
Smaily Carrilho said:

9 March 2017 to 10: 44

Simple and easy to understand tutorial. Thank you very much.

Reply
- Dirceu Resende said:
  
  13 March 2017 to 18: 51
  
  Thanks for the feedback, Smaily. Hug.
  
  Reply
Fernando Faria said:

26 July 2016 to 14: 51

Congratulations on the post. One question: If the table I am going to create the trigger has text fields, how do I control it, since within this trigger is not allowed to use this type of field?

Reply
- Dirceu Resende said:
  
  27 July 2016 to 20: 26
  
  Fernando,
  Good evening.
  
  First, thanks for stopping by. If you have any questions, I recommend that you use VARCHAR (MAX) or NVARCHAR (MAX) instead of TEXT, as directed by Microsoft (references below), as the TEXT, NTEXT, and IMAGE data types are marked as discontinued and are still only present in SQL 2016 for backward compatibility, but may be removed in future editions at any time.
  
  References:
  - https://msdn.microsoft.com/en-us/library/ms191300.aspx
  - https://msdn.microsoft.com/en-us/library/ms187993.aspx
  
  Could I answer your question? Any questions, just talk 🙂
  
  Reply
  - Fernando Faria said:
    
    28 July 2016 to 11: 11
    
    Dirceu good morning,
    
    I made the adjustments according to orientation, converting all fields TEXT to VARCHAR and created a trigger to do some tests. It's working right.
    
    I noticed that the content recorded in the LOG file is the new record, ie with the changes made to the table, but I have no way of knowing which field has changed. Is there any way to record both contents (old and new record) so that I can compare and see what has changed or just record the fields that have changed?
    
    Thanks again
    
    Hug
    Fernando
    
    Reply
    - Alexandre Paiva said:
      
      13 March 2017 to 18: 35
      
      Have you consulted the option of using SQLServer CDC (Native)? It can be used in conjunction with triggers, and allows the query you want, but with a little more work to select.
      
      Excellent tutorial: https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-cdc-in-sql-server-2008/
      
      Reply
      - Dirceu Resende said:
        
        13 March 2017 to 18: 55
        
        Alexandre, I'm glad to see you here. Cdc is a great alternative to using triggers. Soon will have a post about it.