Instead of Update Trigger in SQL Server

Instead of Update Trigger

To Create INSTEAD OF UPDATE 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 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 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')

--Now crate 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 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 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.

Created All tables for Instead of Update Triggers

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

create trigger trtblEmployeeInsteadOfUpdate
on tblEmployee
INSTEAD OF update
AS
  BEGIN
     Declare @EmpId int,               
             @EmpMessage varchar(200),
             @Name varchar(30), @Gender varchar(8),
             @DepartmentId int;
     select  @EmpId =D.Id 
             from deleted D;
     select  @Name = I.Name, 
             @Gender = I.Gender,
             @DepartmentId= DeparmentId from Inserted I;
             
      
     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
            if(UPDATE(Name))
              begin
                 update tblEmployee set Name = @Name where Id= @EmpId
              end
             If(UPDATE(Gender))
               begin
                 update tblEmployee set Gender =@Gender where Id= @EmpId
               end
             If(UPDATE(DeparmentId))
               begin
                 update tblEmployee set DeparmentId =@DepartmentId where Id= @EmpId
               end
             insert into tblAudit values('Employee succefully Update with Id ' + CAST(@EmpId as varchar(5)) + 'and insert into trigger in date' + CAST(Getdate() AS nvarchar(30)))
          End
  END

Now try to Update data from tblEmployee, If we try to update id which is not exists than return message Employee with Id not exits

update tblEmployee set Name= 'janee' where id=5

Show Message Employee Not Exit

Now we try to Update id which is already exists return message successfully row effect

update tblEmployee set Name= 'janee' where id=1

Now check data from tables.

Select all tables After, Instead of  Update Trigger fire

Example 2

For more understand about INSTEAD OF UPDATE 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,
  EmpOldName varchar(30) Not Null,
  EmpNewName varchar(30) Not Null,
  EmpOldGender char(8) Not Null,
  EmpNewGender char(8) Not Null,
  EmpOldDeparmentId int Not Null,
  EmpNewDeparmentId 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 UPDATE Trigger for “tblEmployee”

create trigger trtblEmployeeInsteadOfDeleteByDetail
ON tblEmployee
INSTEAD OF UPDATE
As
 BEGIN
     Declare @EmpId int, 
             @OldEmpName varchar(30), @NewEmpName varchar(30),            
             @OldEmpGender varchar(8), @NewEmpGender varchar (8),
             @OldEmpDepatmentId int, @NewEmpDepatmentId int,
             @EmpMessage varchar(300),@AuditAction varchar(2000),
             @ActionTakenBy varchar(100),@AuditTime varchar(50),
             @ServerName varchar(100), @ServerInstanceName Varchar(100);
     select  @EmpId=D.Id,             --Id Not Change
             @OldEmpName = D.Name, 
             @OldEmpGender = D.Gender,
             @OldEmpDepatmentId = D.DeparmentId from deleted D;
     select  @NewEmpName = I.Name, --delete not only keep id column value, keep all column value for which id we want delete. 
             @NewEmpGender= I.Gender, @NewEmpDepatmentId= I.DeparmentId
             from inserted I
      
     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 Update Triggers in SQL Server not fire because Employee Not Exists' 
           RollBack
       End
       Else
          Begin
             SET @AuditAction='Update Record '
             If(@OldEmpName <> @NewEmpName)
               begin
                  set @AuditAction = @AuditAction + 'Name from ' + @OldEmpName + 'to ' + @NewEmpName + ', '
                  update tblEmployee set Name= @NewEmpName where id=@EmpId
               end
             If(@OldEmpGender <> @NewEmpGender)
               begin
                 set @AuditAction =@AuditAction + 'Gender from ' + @OldEmpGender + 'to ' + @NewEmpGender + ', '
                 update tblEmployee set Gender= @NewEmpGender where id=@EmpId
               end
             If(@OldEmpDepatmentId <> @NewEmpDepatmentId)
               begin
                 set @AuditAction =@AuditAction + 'DeaparmentId from ' + CAST(@OldEmpDepatmentId as varchar(10)) + 'to ' + CAST(@NewEmpDepatmentId As varchar(10))+ ', '
                 update tblEmployee set DeparmentId= @NewEmpDepatmentId where id=@EmpId
               end
    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, EmpOldName, EmpNewName, EmpOldGender, EmpNewGender,
                                 EmpOldDeparmentId, EmpNewDeparmentId, AuditAction,
                                ActionTakenBy, AuditTime,ServerName,ServerInstanceName)
                         values(@EmpId,@OldEmpName, @NewEmpName, @OldEmpGender, @NewEmpGender,
                                @OldEmpDepatmentId, @NewEmpDepatmentId, @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 Update Triggers in SQL Server'
          End
 END

Now try to Update data from tblEmployee, If we try to Update id which is not exists than return message Employee with Id not exits

update tblEmployee set Name= 'jhone', Gender='Male' where id=5

But If we try to Update id which is already exists return message successfully row effect

update tblEmployee set Name= 'jhone', Gender='Male' where id=1

Update Tables

Now check data from tables.

previous Related Post