DML Trigger Real-Time Examples in SQL Server

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 before proceeding to this article where we discussed the Inserted and Deleted tables in detail. Here, we are going to implement the audit example using the trigger. So, whenever we insert, update, or delete any data from a table, then we need to make an entry into the audit table.

We are going to use the following Employee table to understand this example.

DML Trigger Real-Time Examples in SQL Server

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

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 insert trigger. Now let us insert a record into the Employee table.

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 inserted into the EmployeeAudit table. 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

Example: AFTER TRIGGER for DELETE event

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 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 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

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.

Leave a Reply

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