Friday, 7 June 2013

trigger on delete


Hi,
                 In previous article we will see what is trigger? and types of trigger? , example of
after update ,and instead of update.and example of after insert and instead of insert. now in
this article we will see how to write a trigger after delete and instead of delete 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 delete Trigger::


Create TRIGGER trg_After_delete ON [dbo].[temptrigger]
FOR delete
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from deleted i;
select @empname=i.sname from deleted i;
insert into TRIGGER_data(id,sname) values(@empid,@empname);
PRINT 'AFTER delete trigger fired.'
GO

Check OUTPUT::


insert into temptrigger (id,sname )values(1,'san')
delete temptrigger where id=1
select * from temptrigger
select * from TRIGGER_data

B)INSTEAD OF delete Trigger::


CREATE TRIGGER trg_delete_instead ON [dbo].[temptrigger]
instead of delete
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from deleted i;
select @empname=i.sname from deleted i;
insert into TRIGGER_data(id,sname)values(@empid,@empname);
PRINT 'instead delete trigger fired.'
GO

Check OUTPUT::


insert into temptrigger (id,sname )values(1,'san')
delete temptrigger where id=1
select * from temptrigger
select * from TRIGGER_data

No comments:

Post a Comment

8451831077