469,623 Members | 1,850 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

difference between FOR INSERT and AFTER INSERT triggers

I've been reading the docs and playing around, but I'm still not
getting the difference. For instance,

create table a(i int check(i>0))
create table a_src(i int)
go
create unique index ai on a(i) with IGNORE_DUP_KEY
go
insert into a_src values(1)
insert into a_src values(1)
insert into a_src values(2)
--insert into a_src values(-1)
go
create trigger a4ins on a
for insert
as
select * from inserted
go
create trigger afterins on a
after insert
as
select * from inserted
go
insert into a select * from a_src
go
drop table a
drop table a_src
I'm getting

i
-----------
1
2

(2 row(s) affected)

Server: Msg 3604, Level 16, State 1, Procedure a4ins, Line 4
Duplicate key was ignored.
i
-----------
1
2

(2 row(s) affected)

even the inserted quasi tables are identical.
If I uncomment insert into a_src values(-1), I'm getting

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint
'CK__a__i__58FC18A6'. The conflict occurred in database 'ABC_1COMPEE',
table 'a', column 'i'.
The statement has been terminated.

without any output from either trigger.
So,
in which situations will FOR INSERT be useful while AFTER INSERT won't
do?
in which situations will AFTER INSERT be useful while FOR INSERT won't
do?

Jul 23 '05 #1
2 16587
Ford Desperado (fo************@yahoo.com) writes:
I've been reading the docs and playing around, but I'm still not
getting the difference.


That is because there isn't any!

If memory servers, FOR was the original syntax. I suspect that AFTER
has been added to align with ANSI standards. FOR is not very precise,
where as AFTER tells us that the trigger fires after the statement.
That in difference to BEFORE and INSTEAD OF triggers. (Of which SQL
Server has the latter, but not the former.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
thanks Erland

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

46 posts views Thread by dunleav1 | last post: by
4 posts views Thread by Dabbler | last post: by
7 posts views Thread by anu b | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.