How to Monitor Data Changes in SQL Server Tables with Triggers

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, or DELETE statement affects a table or view.
• DDL triggers fire when a CREATE, ALTER, or DROP 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 key
• TableName: what table will be audited
• Activity: 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.