Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-21 : 14:02:57

Hi

Please any one explain me clearly...bcoz am not able to get correctly.

if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 14:27:11

I think you stated it pretty clearly yourself.
Have you tried to test this? How about this:
In all cases the instead of insert fired first without any violations, the instead of insert was not fired (again) when the actual insert occured, and the after insert didn't fired when constraints were violated during the actual insert.


use tempdb
go
create table junk (i int primary key clustered, c char(1) check(c = 'c'))
go
create trigger tr_junk_inI on junk instead of insert
as
begin
       print 'instead of insert'
       insert junk (i, c)
       select i, c from inserted
end

go
create trigger tr_junk_aI on Junk after insert
as
begin
       print 'after insert'
end

go

print 'insert 1'
insert junk (i, c) values (1, 'c')
go
print ' '
print ' '
print 'insert 2'
insert junk (i, c) values (2, 'd')
go
print ' '
print ' '
print 'insert 3'
insert junk (i, c) values (1, 'c')

go
drop table junk


OUTPUT:
insert 1
instead of insert
after insert

(1 row(s) affected)

(1 row(s) affected)
 
 
insert 2
instead of insert
Msg 547, Level 16, State 0, Procedure tr_junk_inI, Line 5
The INSERT statement conflicted with the CHECK constraint "CK__junk__c__42CCE065". The conflict occurred in database "tempdb", table "dbo.junk", column 'c'.
The statement has been terminated.
 
 
insert 3
instead of insert
Msg 2627, Level 14, State 1, Procedure tr_junk_inI, Line 5
Violation of PRIMARY KEY constraint 'PK__junk__41D8BC2C'. Cannot insert duplicate key in object 'dbo.junk'.
The statement has been terminated.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources