- Audit on SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement audit and control of logins (Logon Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)
- SQL Server - Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
- SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes to tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to Avoid Brute Force Attacks on Your Database
- SQL Server Security Checklist - An SP with more than 70 security items to validate your database.
- SQL Server - How to know the date of a user's last login
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
- SQL Server – Beware of server role securityadmin! Using privilege elevation to become sysadmin
- SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.
- SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts
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),
INSERT INTO Testes.dbo.Cliente
('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(),
-- Dados da tabela original
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
SET NOCOUNT ON
@Login VARCHAR(100) = SYSTEM_USER,
@HostName VARCHAR(100) = HOST_NAME(),
@Data DATETIME = GETDATE()
IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))
INSERT INTO Testes.dbo.Cliente_Log
SELECT @Data, @Login, @HostName, 'UPDATE', *
IF (EXISTS(SELECT * FROM Inserted))
INSERT INTO Testes.dbo.Cliente_Log
SELECT @Data, @Login, @HostName, 'INSERT', *
INSERT INTO Testes.dbo.Cliente_Log
SELECT @Data, @Login, @HostName, 'DELETE', *
And now let's simulate some changes in the base:
INSERT INTO Testes.dbo.Cliente
VALUES ('Bartolomeu', '1975-05-28', 6158.74)
SET Salario = Salario * 1.5
WHERE Nome = 'Bartolomeu'
DELETE FROM Testes.dbo.Cliente
WHERE Nome = 'André'
SET Salario = Salario * 1.1
WHERE Id_Cliente = 2
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:
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.
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?
You can save the user in the hot table, replicating to the log.
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?
To save the information that was replaced in the update, should I change "INSERTED" to "DELETED" after the from in the update clause?
Great tutorial, congratulations, helped me a lot here.
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!
Simple and objective tutorial!
Thank you very much!
Simple and easy to understand tutorial. Thank you very much.
Thanks for the feedback, Smaily. Hug.
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?
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.
Could I answer your question? Any questions, just talk 🙂
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?
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/
Alexandre, I'm glad to see you here. Cdc is a great alternative to using triggers. Soon will have a post about it.