How to "Monitor Data Changes in SQL Server Tables with Triggers"
Use SQL triggers to keep track of the changes made to your data
SQL Server is a powerful and popular relational database management system that supports various data operations. However, sometimes you may want to keep track of the changes made to your data, such as when, who, and what was inserted, updated, or deleted in a table. This can be useful for auditing, debugging, or reporting purposes.
One way to achieve this is by using triggers, which are special stored procedures that execute automatically in response to certain events. In this article, we will show you how to create and use triggers to monitor data changes in SQL Server tables.
There are three types of triggers in SQL Server: DML, DDL, and Logon triggers.
- DML triggers fire when an
INSERT
,UPDATE
, orDELETE
statement affects a table or view. - DDL triggers fire when a
CREATE
,ALTER
, orDROP
statement affects a database object. - Logon triggers fire when a user logs on to the database.
To create a trigger, you need to use the CREATE TRIGGER
statement, which has the following syntax:
CREATE TRIGGER trigger_name
ON table_name
AFTER | INSTEAD OF {INSERT | UPDATE | DELETE}
AS
{sql_statements}
The trigger_name
is the name of the trigger, which must be unique within the database. The table_name
is the name of the table or view that the trigger is associated with. The AFTER
keyword specifies that the trigger should execute after the triggering action, while the INSTEAD OF
keyword specifies that the trigger should execute instead of the triggering action. The INSERT
, UPDATE
, and DELETE
keywords specify the type of data operation that activates the trigger. The sql_statements
are the statements that define the logic of the trigger.
For example, suppose we have a table called Employees
with the following columns: EmployeeID
, FirstName
, LastName
, and Salary
. We want to create a trigger that logs every change made to this table in another table called Employees_Audit
with the following columns: AuditID
, EmployeeID
, Action
, OldSalary
, NewSalary
, and DateTime
.
We can use the following code to create the DML trigger:
First, you need create one table for stored information, I create one Table
with fields: ID
(int
), TableName
(varchar-100
), Activity
(varchar-20
). Date_Time
(datetime
- get current time)
ID
: primary keyTableName
: what table will be auditedActivity
:Insert
/update
/delete
Data_Time
: stored current time when one action is active to this table
CREATE TRIGGER Employees_Trigger
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- Declare variables
DECLARE @AuditID int;
DECLARE @EmployeeID int;
DECLARE @Action varchar(10);
DECLARE @OldSalary decimal(18,2);
DECLARE @NewSalary decimal(18,2);
DECLARE @DateTime datetime;
-- Get the current date and time
SET @DateTime = GETDATE();
-- Check if it is an INSERT action
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'INSERT'
SET @Action = 'INSERT';
-- Get the inserted employee ID and salary
SELECT @EmployeeID = EmployeeID,
@NewSalary = Salary
FROM inserted;
-- Set the old salary to null
SET @OldSalary = NULL;
-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END
-- Check if it is an UPDATE action
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'UPDATE'
SET @Action = 'UPDATE';
-- Get the updated employee ID and salaries
SELECT @EmployeeID = i.EmployeeID, @OldSalary = d.Salary, @NewSalary = i.Salary
FROM inserted i
INNER JOIN deleted d
ON i.EmployeeID = d.EmployeeID;
-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END
-- Check if it is a DELETE action
IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'DELETE'
SET @Action = 'DELETE';
-- Get the deleted employee ID and salary
SELECT @EmployeeID = EmployeeID, @OldSalary = Salary
FROM deleted;
-- Set the new salary to null
SET @NewSalary = NULL;
-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END
END;
Now, whenever we insert
, update
, or delete
a record in the Employees
table, the trigger will insert a corresponding record in the Employees_Audit
table with the relevant information.
To test the trigger, we can use the following statements:
-- Insert a new employee
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (101, 'John', 'Doe', 50000);
-- Update an existing employee's salary
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 101;
-- Delete an existing employee
DELETE FROM Employees
WHERE EmployeeID = 101;
The result of these statements can be seen in the Employees_Audit
table:
| AuditID | EmployeeID | Action | OldSalary | NewSalary | DateTime |
| ------- | ---------- | ------ | --------- | --------- | -------- |
| 1 | 101 | INSERT | NULL | 50000.00 | 2023-08-07 12:20:15.123 |
| 2 | 101 | UPDATE | 50000.00 | 60000.00 | 2023-08-07 12:21:23.456 |
| 3 | 101 | DELETE | 60000.00 | NULL | 2023-08-07 12:22:34.789 |
As you can see, the trigger has successfully logged every change made to the Employees
table.
In conclusion, triggers are a useful feature of SQL Server that allow you to monitor data changes in your tables. You can use them to perform various tasks, such as auditing, debugging, or reporting. However, you should also be careful when using triggers, as they can affect the performance and integrity of your database if not designed and tested properly.
Another example:
Next, create one trigger with structure:
CREATE TRIGGER trigger_name
ON table_name
AFTER [Update] , [Insert], [Delete]
AS
BEGIN
...
END
inserted
is a template table when user inserts any row to the table, will be stored.deleted
is a template table when user deletes any row to the table, will be stored.
Thank you for reading this post. I hope you found it helpful and easy to follow. If you have any feedback or questions about How to "Monitor Data Changes in SQL Server Tables with Triggers", please share them in the comments below. I would love to hear from you and discuss this topic further.