AFTER DELETE Triggers in SQL Server

The SQL Server AFTER Delete Triggers will fire after the completion of Delete operation on a table. SQL After DELETE Triggers not Supported on Views. For this SQL Server AFTER Delete Triggers demonstration, We use the below-shown tables.

Here, our task is to create SQL Server AFTER DELETE TRIGGER on this Employee table. And by using this SQL Server AFTER Delete Trigger we want to Insert the deleted records from the Employee table into the Employee Audit table

After DELETE Triggers in SQL Server 0

and our Employee Table Audit is empty.

After DELETE Triggers in SQL Server 1

After DELETE Triggers in SQL Server Example

In this example, we will show you how to create an After delete Triggers in SQL Server. And here, we are using the CREATE TRIGGER Statement to create a trigger on the Employee table.

TIP: You can refer TRIGGERSAFTER INSERT TRIGGERS, Views, and AFTER UPDATE TRIGGERS  articles in SQL Server.

Remember, After delete trigger will fire after the completion of Delete operation on Employee table. Once it completes deleting the records from the Employee table, it will start inserting/deleting from the Employee audit table. And if it fails to delete from the Employee table, then it won’t insert into the Audit table.

-- Example for After DELETE Triggers in SQL Server

CREATE TRIGGER AfterDELETETrigger on [EmployeeTable]
FOR DELETE 
AS DECLARE @EmpID INT,
           @EmpName VARCHAR(50),
	   @EmpEducation VARCHAR(50),
	   @EmpOccupation VARCHAR(50),
	   @EmpYearlyIncome DECIMAL (10, 2), 
	   @EmpSales DECIMAL (10, 2);

SELECT @EmpID = del.ID FROM DELETED del;
SELECT @EmpName = del.Name FROM DELETED del;
SELECT @EmpEducation = del.Education FROM DELETED del;
SELECT @EmpOccupation = del.Occupation FROM DELETED del;
SELECT @EmpYearlyIncome = del.YearlyIncome FROM DELETED del;
SELECT @EmpSales = del.Sales FROM DELETED del;
  
INSERT INTO [EmployeeAuditTable]( 
       ID
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Deleted Time])
VALUES (@EmpID,
        @EmpName,
	@EmpEducation,
	@EmpOccupation,
	@EmpYearlyIncome,
	@EmpSales,
	CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)), 
	CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)), 
	GETDATE());
PRINT 'We Successfully Fired the AFTER DELETE Triggers in SQL Server.'
GO
After DELETE Triggers in SQL Server 2

First, we used the DECLARE Statement to declare the required variables

DECLARE @EmpID INT,
        @EmpName VARCHAR(50),
        @EmpEducation VARCHAR(50),
	@EmpOccupation VARCHAR(50),
	@EmpYearlyIncome DECIMAL (10, 2), 
	@EmpSales DECIMAL (10, 2);

Next, we used the SELECT Statement to select the deleted records. The following statements will select only one record from deleted rows.

SELECT @EmpID = del.ID FROM DELETED del;
SELECT @EmpName = del.Name FROM DELETED del;
SELECT @EmpEducation = del.Education FROM DELETED del;
SELECT @EmpOccupation = del.Occupation FROM DELETED del;
SELECT @EmpYearlyIncome = del.YearlyIncome FROM DELETED del;
SELECT @EmpSales = del.Sales FROM DELETED del;

Next, we used the INSERT Statement to insert the selected values into the Employee Audit table. Here, the following statements will return the Machine Name and the server name. This information might be helpful for the audit

SERVERPROPERTY('MachineName'), 
SERVERPROPERTY('ServerName')

Let me show you the newly created SQL Server AFTER Delete Trigger in the Object Explorer. For this, Go to the SQL Tutorial Database -> Find and expand the Employee Table -> and then expand the Triggers Folder

After DELETE Triggers in SQL Server 3

For the demonstration purpose, we are deleting the records whose Occupation = ‘Clerical’ to check whether the After delete Trigger is triggered or not.

-- SQL AFTER DELETE Trigger Example

DELETE FROM [EmployeeTable]
	WHERE [Occupation] = N'Clerical'
After DELETE Triggers in SQL Server 4

As you can see that, our SQL Server AFTER Delete Triggers is triggered and also inserted one record into the Audit Table. Please use the following SQL Query to check the Employee table

-- SQL AFTER DELETE Trigger Example
SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]
After DELETE Triggers in SQL Server 5

Next, check the records in the Employee Audit table using the following query.

-- SQL AFTER DELETE Trigger Example
SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Deleted Time]
  FROM [EmployeeAuditTable]
After DELETE Triggers in SQL Server 6

From the above screenshot, you can see that our employee Audit Table is returning a single record. Because, in our Trigger definition, we are selecting only one record for each delete operation.

After DELETE Triggers in SQL Server Example 2

This example shows how to insert all the deleted records into the employee audit table (triggered table) using the After delete Triggers in SQL Server. And here, we will modify the trigger that we created in our previous example.

-- Example for After DELETE Triggers in SQL Server

CREATE TRIGGER AfterDELETETrigger on [EmployeeTable]
FOR DELETE 
AS 
INSERT INTO [EmployeeAuditTable]( 
       ID
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Deleted Time])
  SELECT ID
	,[Name]
	,[Education]
	,[Occupation]
	,[YearlyIncome]
	,[Sales]
	,CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)) 
	,CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)) 
	,GETDATE()
FROM DELETED;
PRINT 'We Successfully Fired the AFTER DELETE Triggers in SQL Server.'
GO
After DELETE Triggers in SQL Server 7

As you can see from the above, we are using the INSERT INTO SELECT Statement to select all the records deleted from the Employee table. Then we are inserting those records into the Employee Audit table.

Next, let me delete the records from the employee table, whose Occupation is Management, or Education = High School

-- SQL Server AFTER DELETE Trigger Example

DELETE FROM [EmployeeTable]
	WHERE [Occupation] = N'Management' OR
		[Education] = N'High School'
After DELETE Triggers in SQL Server 8

From the above image, you can see that the after delete trigger fired. And also inserted all the records into the audit table. Let us see the Employee table

After DELETE Triggers in SQL Server 9

Next, check with the Employee Audit table.

After DELETE Triggers in SQL Server 10

From the above screenshot, you can see that the trigger has inserted all the records.

Categories SQL