Instead of delete trigger in sql server

INSTEAD OF INSERT Trigger

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

--We create tblDeparment 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
)

--Now insert data into tblDeparment 

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

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

--Now insert data into 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.
We will apply trigger for these tables.

Example 1 Now create table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have table are given below with data.

All table are given

Now create the INSTEAD OF DELETE Trigger for “tblEmployee”.

create trigger trtblEmployeeInsteadOfDelete
on tblEmployee
INSTEAD OF DELETE
AS
  BEGIN
     Declare @EmpId int,               
             @EmpMessage varchar(200);
     select  @EmpId =D.Id 
             from deleted D
      
     If(Not exists(Select Id from tblEmployee where Id = @EmpId))   --If Id not Exists in Cloumn than through message. you can check other condition like departmentId =1 or Gender=Male, than you can print message you can not delete
       Begin
           SET @EmpMessage  = 'Employee With Id ' + CAST(@EmpId as varchar(5)) + 'Not Exists in the DataBase'
           Raiserror(@EmpMessage, 16, 1)
           Print 'Employee Not Exists'
           RollBack
       End
       Else
          Begin
             delete from tblEmployee where Id= @EmpId     --delete command      
             insert into tblAudit values('Employee succefully Delete with Id ' + CAST(@EmpId as varchar(5)) + 'and insert into trigger in date' + CAST(Getdate() AS nvarchar(30)))
          End
  END

Now try to delete data from tblEmployee.
If we try to delete id which is not exists than return message Employee with Id not exits delete from tblEmployee where Id=5.

Try to delete from table

Now we try to delete id which is already exists return message succefully row effect delete from tblEmployee where Id=4.

Try to delete from table

Now check data from tables.

Selete table after fire instead of delete trigger

Example 2

For more understand about INSTEAD OF 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,
  EmpGender char(8) Not Null,
  EmpDeparmentId int Not Null,
  AuditAction varchar(1000) 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 the INSTEAD OF DELETE Trigger for “tblEmployee”.

create trigger trtblEmployeeInsteadOfDeleteByDetail
ON tblEmployee
INSTEAD OF DELETE
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentId int,
             @EmpMessage varchar(300),@AuditAction varchar(2000),
             @ActionTakenBy varchar(100),@AuditTime varchar(50),
             @ServerName varchar(100), @ServerInstanceName Varchar(100);
             
     select  @EmpId=D.Id, @EmpName = D.Name, --delete not only keep id column value, keep all column value for which id we want delete. 
             @EmpGender= D.Gender, @EmpDepatmentId= D.DeparmentId
             from deleted D
      
     If not exists (Select Id from tblEmployee where Id = @EmpId)   --If Id not Exists in Cloumn than through message, you can check other condition like departmentId =1 or Gender=Male, than you can print message you can not delete
       Begin
           SET @EmpMessage  = 'Employee With Id ' + CAST(@EmpId as varchar(5)) + 'Not Exists in the DataBase.'
           Raiserror(@EmpMessage, 16, 1)
           Print 'INSTEAD OF DELETED Triggers in SQL Server not fire because ' + @EmpMessage
           RollBack
       End
       Else
          Begin
             delete from tblEmployee where Id= @EmpId --delete command                             
             
             SET @AuditAction='Delete Record'
             SET @ActionTakenBy= SYSTEM_USER
             SET @ServerName = CAST(SERVERPROPERTY('ServerName') as varchar(100))
             SET @ServerInstanceName = CAST(SERVERPROPERTY('MachineName') as varchar)
             insert into tblAuditDetailsBy(EmpId, EmpName, EmpGender, EmpDeparmentId, AuditAction,
                                           ActionTakenBy, AuditTime, ServerName, ServerInstanceName)
                                    values(@EmpId,@EmpName, @EmpGender, @EmpDepatmentId, @AuditAction,
                                           @ActionTakenBy, GETDATE(), @ServerName, @ServerInstanceName)
             PRINT 'Successfully Delete Data with ID= ' + CAST(@EmpId as varchar(5)) + CAST(Getdate() AS nvarchar(30));
             PRINT 'Fired the INSTEAD OF Delete Triggers in SQL Server'
          End
 END

Now try to delete data from tblEmployee.
If we try to delete id which is not exists than return message Employee with Id not exits delete from tblEmployee where Id=5.
And if try to delete id which is already exists return message successfully row effect

delete from tblEmployee where Id=2

Delete data from table than Instead of delete trigger fire

 Now check data from tables.

previous Related Post