AFTER DELETE Trigger in SQL Server with examples

AFTER DELETE Trigger

To Create AFTER Triggers first of all we need to create a table (but we create two table) for which we apply trigger and need another table that hold all activity in main table.

--We Create below tbldepartment Table

create table tblDeparment
(
 Id int identity(1,1) primary key not null,
 DeparmentName varchar(30) not null,
 Location Char(8) not null,
 DeparmentHead varchar(20) not null
)

--We Insert followings datas in tblDepartment

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--We create another table tblEmployee

create table tblEmployee
(
 Id int identity(1,1) primary key not null,
 Name varchar(30) not null,
 Gender Char(8) not null,
 DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)
)

--insert data into table tblEmployee
insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee“  which are inter-connected through Foreign key constraint.
Now we will apply trigger for these tables.

Example 1
First of all we need to create table that hold activity in main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)     --keep all activity in his cloumn
) 

Now we have tables are given below with data.

Now create the AFTER DELETE Trigger for “tblEmployee”.

Create trigger trtblEmployeeForDelete
on tblEmployee
For Delete
As
Begin
    Declare @Id int
    Select @Id = Id from deleted         --Deleted table use by SQL Server to keep copy of Row which just delete from actual table, the structure of the deleted table is identical to the structure of the actual table
    insert into tblAudit values('An Exiting Employee With Id = ' + cast(@Id as varchar(10)) + 'is Deleted at' + cast(GETDATE() as nvarchar(20)))

--CAST is used to convert int type Id to nvarchar that concatenate with "New Employee with Id"
End

Now try to delete data from “tblEmployee” than "trtblEmployeeForDelete" trigger automatically fire and data insert into "tblAudit" and also show in message for affected rows.

delete from tblEmployee where Id=4

Now check data from tables.

Example 2
For more understand about AFTER DELETE Trigger we take another trigger example.
First we create another table that hold all activity of main table done by user.

Create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  Gender char(8) Not Null,
  DeparmentId int Not Null,
  AuditAction varchar(30) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create AFTER DELETE Trigger.

CREATE TRIGGER trtblEmployeeForAfterDelete
On tblEmployee
AFTER DELETE                 -- We can use AFTER instead of FOR, in above Query we use FOR
AS
 Begin
 declare @EmpId int;        --Declare variables
 declare @EmpName varchar(30);
 declare @EmpGender varchar(8);
 declare @EmpDepartmentId int;
 declare @EmpAuditAction varchar(100);
 declare @ActionTakenBy varchar(100)
 declare @ServerName varchar(100);
 declare @ServerInstanceName varchar(100);
    
 select @EmpId=D.Id from deleted D;     --Select Id from deleted and assign to @EmpId. (D is alias name for deleted table)
 select @EmpName=D.Name from deleted D; 
 select @EmpGender=D.Gender from deleted D;
 select @EmpDepartmentId=D.DeparmentId from deleted D; 
 set @EmpAuditAction='Deleted Record ';
 set @ActionTakenBy = SYSTEM_USER;
 set @ServerName = CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50));          -- select @@SERVERNAME, return server name
 set @ServerInstanceName = CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50));    --SELECT @@servicename, return current machine name
 
 insert into tblAuditDetailsBy (EmpId, EmpName, Gender, DeparmentId, AuditAction,
                                ActionTakenBy, AuditTime,ServerName,ServerInstanceName)
                         values(@EmpId,@EmpName,@EmpGender,@EmpDepartmentId,@EmpAuditAction,
                                @ActionTakenBy,GETDATE(), @ServerName, @ServerInstanceName)

 PRINT 'Successfully Fired the AFTER DELETE Triggers in SQL Server'
End

Now try to delete data from "tblEmployee" than "trtblEmployeeForAfterInsert" trigger automatically fire and data insert into "tblAuditDetailsBy" and also show in message for two row affected.

delete from tblEmployee where Id=2

Now Check data's from tables