instead of delete trigger in SQL Server using views

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 need another table that hold all activity in main table.

--We create table tblDeparment

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
)

--insert data's 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')

--Create 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's 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.
Now create Views.

create View VwTblEmployeeJoinWithtblDepartment
as
select tblEmployee.Id, Name, Gender, DeparmentName
from tblEmployee
join tblDeparment
on tblEmployee.DeparmentId = tblDeparment.Id

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 tables and views are given below with data.

We have all tables for instead of delete trigger using vies

If we try to delete data from view than not delete show an error multiple base table affects.

Try to delete data from view than show an error

So create the INSTEAD OF DELETE Trigger for “VwTblEmployeeJoinWithtblDepartment”

create trigger trtblEmployeeInsteadOfDeatilByView
ON VwTblEmployeeJoinWithtblDepartment
INSTEAD OF DELETE
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentName varchar(20),
             @EmpMessage varchar(200);
     select  @EmpId = D.Id, @EmpName = D.Name, 
             @EmpGender= D.Gender, @EmpDepatmentName= D.DeparmentName
             from deleted D              --Here inserted table keep data which we want insert through view.
      
          If(Not exists(Select id from tblEmployee where id = @EmpId))   --If Alredy Name Exists in Cloumn than through message we can also check by UserName, EmailId etc.
             Begin
              Raiserror('Employee With Id Not Exists in the DataBase.', 16, 1)
              Print 'INSTEAD OF DELETED Triggers in SQL Server not fire.' 
              RollBack
             End
           Else
             Begin
              delete tblEmployee from tblEmployee --delete command 
              join deleted
              on tblEmployee.Id = deleted.Id                         
              insert into tblAudit values('Employee succefully deleted with Id ' + CAST(@EmpId as varchar(5)) + 'in trigger in date' + CAST(Getdate() AS nvarchar(30)))
             End
      
 END

Now delete data from view.

delete from VwTblEmployeeJoinWithtblDepartment where Id=2

Now check tables and view.

select All tables and view after fire trigger

Example 2
For more understand about INSTEAD OF INSERT Trigger by view when multiple base table update so 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,
  EmpDeparmentName varchar(50) 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 “VwTblEmployeeJoinWithtblDepartment”

create trigger trtblEmployeeInsteadOfDeleteByDetailView
ON VwTblEmployeeJoinWithtblDepartment
INSTEAD OF DELETE
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @DeparmentName varchar(50),
             @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, @DeparmentName= D.DeparmentName
             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
           Raiserror('Id does not exists', 16, 1)
           Print 'INSTEAD OF DELETED Triggers in SQL Server not fire because Id does not exists'
           RollBack
       End
       Else
          Begin
              delete tblEmployee from tblEmployee --delete command 
              join deleted
              on tblEmployee.Id = deleted.Id                                      
             
             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, EmpDeparmentName, AuditAction,
                                           ActionTakenBy, AuditTime, ServerName, ServerInstanceName)
                                    values(@EmpId,@EmpName, @EmpGender, @DeparmentName, @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 delete data from view VwTblEmployeeJoinWithtblDepartment.

delete from VwTblEmployeeJoinWithtblDepartment where Id = 2

Try to delete data from view than fire Instead of delete trigger

Now check tables and view.

previous Related Post