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's table will be audit
- Activity: Insert / update / delete
- Data_Time: stored current time when one action 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.
#SQLServer #Triggers #DataChanges #DML #DDL #Logon #Audit #Debug #Report #Insert #Update #Delete
Explore My Other Channel for More Cool and Valuable Insights
👉 Youtube Learn Tech Tips👉 Tiktok
👉 Facebook: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 insert any row to the table, will be store it
deleted is a template table when user delete any row to the table, will be store it