Back to: SQL Server Tutorial For Beginners and Professionals
DML Trigger Real-Time Examples in SQL Server
In this article, I am going to discuss DML Trigger Real-Time Examples in SQL Server. Please read our previous article where we discussed the Inserted and Deleted tables in SQL Server with Examples.
Real-time Examples of DML Trigger in SQL Server:
Here, we are going to implement the audit example using the DML Trigger in SQL Server. So, whenever we INSERT, UPDATE, or DELETE any data from a table, then we need to make an entry into the audit table as well. We are going to use the following Employee table to understand this concept.
Please use the following SQL Script to create and populate the Employee table.
-- Create Employee table CREATE TABLE Employee ( Id int Primary Key, Name nvarchar(30), Salary int, Gender nvarchar(10), DepartmentId int ) GO -- Insert data into Employee table INSERT INTO Employee VALUES (1,'Pranaya', 5000, 'Male', 3) INSERT INTO Employee VALUES (2,'Priyanka', 5400, 'Female', 2) INSERT INTO Employee VALUES (3,'Anurag', 6500, 'male', 1) INSERT INTO Employee VALUES (4,'sambit', 4700, 'Male', 2) INSERT INTO Employee VALUES (5,'Hina', 6600, 'Female', 3)
Along with the above Employee table, we are also going to use the following EmployeeAudit table. Please use the below SQL Script to create the EmployeeAudit table.
-- Create EmployeeAudit Table CREATE TABLE EmployeeAudit ( ID INT IDENTITY(1,1) PRIMARY KEY, AuditData VARCHAR(MAX), AuditDate DATETIME )
Our business requirement is that whenever a new employee is added to the Employee table we want to capture the ID and name of the Employee and store the data into the EmployeeAudit table. The simplest way to achieve this is by using an AFTER TRIGGER for the INSERT event.
Example: AFTER TRIGGER for INSERT Event in SQL Server
The following is an example of AFTER TRIGGER for INSERT event on Employee table to store the inserted employee data on the EmployeeAudit table:
CREATE TRIGGER tr_Employee_For_Insert ON Employee FOR INSERT AS BEGIN -- Declare a variable to hold the ID Value DECLARE @ID INT -- Declare a variable to hold the Name value DECLARE @Name VARCHAR(100) -- Declare a variable to hold the Audit data DECLARE @AuditData VARCHAR(100) -- Get the ID and Name from the INSERTED Magic table SELECT @ID = ID, @Name = Name FROM INSERTED -- Set the AuditData to be stored in the EmployeeAudit table SET @AuditData = 'New employee Added with ID = ' + Cast(@ID AS VARCHAR(10)) + ' and Name ' + @Name -- Insert the data into the EmployeeAudit table INSERT INTO EmployeeAudit (AuditData, AuditDate) VALUES(@AuditData, GETDATE()) END
Once you created the above DML AFTER INSERT Trigger. Now let us insert a record into the Employee table by executing the below INSERT statement.
INSERT INTO Employee VALUES (6, ‘Saroj’, 3300, ‘Male’, 2)
When we execute the above INSERT statement. Immediately after inserting the record into the Employee table, the insert trigger gets fired automatically, and a record is also inserted into the EmployeeAudit table. To verify this, issue a select query against the EmployeeAudit table as shown below
SELECT * FROM EmployeeAudit
Once you execute the above query, it will give you the following output.
Example: AFTER TRIGGER for DELETE Event in SQL Server
Next, we are going to capture the audit information when an employee is deleted from the Employee table. The following is an example for AFTER TRIGGER for DELETE event on the Employee table in SQL Server to store the deleted employee data on the EmployeeAudit table.
CREATE TRIGGER tr_Employee_For_Delete ON Employee FOR DELETE AS BEGIN -- Declare a variable to hold the ID Value DECLARE @ID INT -- Declare a variable to hold the Name value DECLARE @Name VARCHAR(100) -- Declare a variable to hold the Audit data DECLARE @AuditData VARCHAR(100) -- Get the ID and Name from the DELETED table SELECT @ID = ID, @Name = Name FROM DELETED -- Set the AuditData to be stored in the EmployeeAudit table SET @AuditData = 'An employee is deleted with ID = ' + Cast(@ID AS VARCHAR(10)) + ' and Name = ' + @Name -- Insert the data into the EmployeeAudit table INSERT INTO EmployeeAudit (AuditData, AuditDate)VALUES(@AuditData, GETDATE()) END
Once you created the above DML AFTER DELETE Trigger. Now let us DELETE a record from the Employee table by executing the below DELETE SQL statement.
DELETE FROM Employee WHERE ID = 6
So when we execute the above DELETE statement. Immediately after Deleting the row from the Employee table, the delete trigger gets fired automatically, and a row into EmployeeAudit is also inserted. To verify this, issue a select query against the EmployeeAudit table as shown below
SELECT * FROM EmployeeAudit
Once you execute the above query, it will give you the following output.
As we already see, triggers make use of 2 special tables INSERTED and DELETED. The inserted table contains the updated data or new data whereas the deleted table contains the old data or deleted data. The After trigger for the UPDATE event makes use of both the inserted and deleted magic tables.
Example: AFTER TRIGGER for UPDATE Event in SQL Server
Now we are going to capture the audit information when an employee is updated from the Employee table. The following is an example of AFTER TRIGGER for UPDATE event on Employee table to store the updated employee data on the EmployeeAudit table.
CREATE TRIGGER tr_Employee_For_Update ON Employee FOR Update AS BEGIN -- Declare the variables to hold old and updated data DECLARE @ID INT DECLARE @Old_Name VARCHAR(200), @New_Name VARCHAR(200) DECLARE @Old_Salary INT, @New_Salary INT DECLARE @Old_Gender VARCHAR(200), @New_Gender VARCHAR(200) DECLARE @Old_DepartmenttId INT, @New_DepartmenttId INT -- Declare Variable to build the audit string DECLARE @AuditData VARCHAR(MAX) -- Store the updated data into a temporary table SELECT * INTO #UpdatedDataTempTable FROM INSERTED -- Loop thru the records in the UpdatedDataTempTable temp table WHILE(Exists(SELECT ID FROM #UpdatedDataTempTable)) BEGIN --Initialize the audit string to empty string SET @AuditData = '' -- Select first row data from temp table SELECT TOP 1 @ID = ID, @New_Name = Name, @New_Gender = Gender, @New_Salary = Salary, @New_DepartmenttId = DepartmentId FROM #UpdatedDataTempTable -- Select the corresponding row from deleted table SELECT @Old_Name = Name, @Old_Gender = Gender, @Old_Salary = Salary, @Old_DepartmenttId = DepartmentId FROM DELETED WHERE ID = @ID -- Build the audit data dynamically Set @AuditData = 'Employee with Id = ' + CAST(@ID AS VARCHAR(6)) + ' changed' -- If old name and new name are not same, then its changed IF(@Old_Name <> @New_Name) BEGIN Set @AuditData = @AuditData + ' Name from ' + @Old_Name + ' to ' + @New_Name END -- If old Gender and new gender are not same, then its changed IF(@Old_Gender <> @New_Gender) BEGIN Set @AuditData = @AuditData + ' Gender from ' + @Old_Gender + ' to ' + @New_Gender END -- If old Salary and new Salary are not same, then its changed IF(@Old_Salary <> @New_Salary) BEGIN Set @AuditData = @AuditData + ' Salary from ' + Cast(@Old_Salary AS VARCHAR(10))+ ' to ' + Cast(@New_Salary AS VARCHAR(10)) END -- If old Department ID and new Department ID are not same, then its changed IF(@Old_DepartmenttId <> @New_DepartmenttId) BEGIN Set @AuditData = @AuditData + ' DepartmentId from ' + Cast(@Old_DepartmenttId AS VARCHAR(10))+ ' to ' + Cast(@New_DepartmenttId AS VARCHAR(10)) END -- Then Insert the audit data into the EmployeeAudit table INSERT INTO EmployeeAudit(AuditData, AuditDate) VALUES(@AuditData, GETDATE()) -- Delete the current row from temp table, so we can move to the next row DELETE FROM #UpdatedDataTempTable WHERE ID = @ID End End
Now perform the update operation on the Employee table and you will see everything is working as expected.
As of now, we have discussed the For Trigger in SQL Server. So in the next article, I am going to discuss the Instead Of Trigger in SQL Server with examples. Here, in this article, I try to explain the DML Trigger Real-Time Examples in SQL Server. I hope now you understand the need and use of For/After trigger in SQL Server.