Friday 7 June 2013

Trigger on Update


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