Hi,
In previous article we will see what is trigger? and types of trigger? , example of after
insert ,and instead of insert trigger now in this article we will see how to write a trigger after
update and instead of update trigger.
Create a two tables for our better understanding by using below query.
Create table temptrigger ( id varchar(100) ,sname varchar(100))
create table TRIGGER_data ( id varchar(100) ,sname varchar(100))
A)AFTER update Trigger::
Create TRIGGER after_update ON [dbo].[temptrigger]
FOR update
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from inserted i;
select @empname=i.sname from inserted i;
insert into TRIGGER_data
(id,sname)
values(@empid,@empname);
PRINT 'AFTER update trigger fired.'
GO
Check OUTPUT::
insert into temptrigger (id,sname )values(1,'san')
update temptrigger set id=2 ,sname='name' where id=1
select * from temptrigger
select * from TRIGGER_data
B)INSTEAD OF Update Trigger::
CREATE TRIGGER instead_update ON [dbo].[temptrigger]
instead of update
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from inserted i;
select @empname=i.sname from inserted i;
insert into TRIGGER_data
(id,sname)
values(@empid,@empname);
PRINT 'AFTER INSERT trigger fired.'
GO
Check OUTPUT::
insert into temptrigger (id,sname )values(1,'san')
update temptrigger set id=2 ,sname='name' where id=1
select * from temptrigger
select * from TRIGGER_data
No comments:
Post a Comment
if you have any doubt any suggestions do comment