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 exitsupdate tblEmployee set Name= 'janee' where id=5
 |
| Show Message Employee Not Exit |
update tblEmployee set Name= 'janee' where id=1Now 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 exitsupdate tblEmployee set Name= 'jhone', Gender='Male' where id=5But 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.
