DML Trigger Real-Time Examples in SQL Server

DML Trigger Real-Time Examples in SQL Server

In this article, I will discuss DML Trigger Real-Time Examples in SQL Server. Please read below articles before proceeding to this article.

DML Triggers in SQL Server

Magic Tables in SQL Server Triggers

We are going to use the below Employee table to understand the Triggers in SQL server.

DML Trigger Real-Time Examples in SQL Server

Please use below SQL Script to create and populate the Employee table with some test data.

-- 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 below 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 in the EmployeeAudit table. The simplest way to achieve this is by using an AFTER TRIGGER for the INSERT event.

Example1: AFTER TRIGGER for INSERT event

The below example is for 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

Let’s insert a record into the Employee table.

INSERT INTO Employee VALUES (6,’Saroj’, 3300, ‘Male’, 2)

So when we execute the above INSERT statement. Immediately after inserting the row into the Employee table the 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

It will give us the below output.

DML Trigger Real-Time Examples in SQL Server

Example2: AFTER TRIGGER for DELETE event

Now we are going to capture the audit information when an employee is deleted from the table Employee.

The below example is for AFTER TRIGGER for DELETE event on Employee table 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

Let’s Delete a record from the Employee table.

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

It will give us the below output.

DML Trigger Real-Time Examples in SQL Server

As we already discussed, triggers make use of 2 special tables INSERTED and DELETED which is also called as Magic Tables. 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 UPDATE event makes use of both the inserted and deleted magic tables. 

Example3: AFTER TRIGGER for UPDATE event

Now we are going to capture the audit information when an employee is updated from the Employee table.

The below example is for AFTER TRIGGER for UPDATE event on Employee table to store the updated employee data on the EmployeeAudit table:

Now perform some update operation on the Employee table and see everything is working as expected.

As of now, we have discussed the For DML Trigger. So in the next article, I will discuss the Instead Of Trigger in SQL Server.

SUMMARY

In this article, I try to explain the DML Trigger Real-Time Examples in SQL Server. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *