Friday 7 June 2013

Trigger on insert


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


Create TRIGGER trg_After_insert ON [dbo].[temptrigger]
FOR insert
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 temptrigger (id,sname)values(1,'sandip')
select * from temptrigger
select * from TRIGGER_data

B)INSTEAD OF insert Trigger::


CREATE TRIGGER trg_insert_instead ON [dbo].[temptrigger]
instead of insert
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 'INSTEAD OF insert Trigger.'
GO

Check OUTPUT::


insert temptrigger (id,sname)values(1,'sandip')
select * from temptrigger
select * from TRIGGER_data

No comments:

Post a Comment

if you have any doubt any suggestions do comment