SQL Server after and insted of Triggers

3
12

In SQL Server (2000 onwards) twotypes of triggers mainly used

1. AFTER Trigger

2.Insted of trigger

the former one is commenly used as FOR

ie

create trigger trigger1 on table1 FOR INSERT as ….

This  works following the triggering action

later is INSTED OF Trigger syntaxt as follows

create trigger trigger1 on table1 INSTEAD OF INSERT as ….

in this case triggering action is INSERT and because of using this INSTEAD OF Clause Insert will not taking place and steps in this trigger only will get executed

Detailing

create table TABLE1
(
c_id int,
c_name varchar(30),
c_phone varchar(10)
)

 

create trigger table1_after
ON TABLE1
FOR
INSERT
as
print ‘inserted’

insert into TABLE1 select 1,’name1′,’phone1′

RESULT
 
inserted

(1 row(s) affected)
select * from TABLE1

RESULT
——-

c_id        c_name                         c_phone
———– —————————— ———-
1           name1                          phone1

(1 row(s) affected)

drop trigger table1_after
delete table1

INSTED OF TRIGGER IN ACTION

create trigger table1_insteadof
ON TABLE1
INSTEAD OF
INSERT
as
print ‘not inserted’

insert into TABLE1 select 1,’name1′,’phone1′

RESULT
 

not inserted

(1 row(s) affected)

select * from TABLE1

RESULT
——-
c_id        c_name                         c_phone
———– —————————— ———-

(0 row(s) affected)

3 COMMENTS

  1. hellow…can u help me no data charges in my facebook.i injoy to using it.last time i got a free in website facebook.now im very sad is gone.i love very much facebook to play very exitement and enjoy to share to my famaly and friend.and i got a lot of new friend.

LEAVE A REPLY

Please enter your comment!
Please enter your name here