Challenge #4 - 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 2000 Forums

Transact-SQL (2000)

Challenge #4 - Trigger

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-21 : 17:42:52

Why the 2nd row never get inserted?

create table t (n int primary key)
go

create trigger tr on t
for insert
as
commit tran
return
go

insert into t select 5
insert into t select 6
go

select * from t
drop trigger tr
drop table t
go

"Don't ask a question if you won't understand the answer"

Granick
Starting Member

46 Posts

Posted - 2003-11-21 : 18:37:36

I am not sure of the exact reasoning on this, but the commit must be finalizing the implied transaction, and since there is no "go" between the 2 INSERT statements, and it is being finalized before the second one ever fires, it is not being done.

I put a SELECT * FROM inserted into the Trigger, and it is only being fired once. If you put the go between the inserts, and it fires twice.

Shannon

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-21 : 18:40:03

Unmatched transaction.
A trigger is run inside a transaction, your commit gives a trancount of 0 which gets decremented on completion of the trigger to -1 which is invalid.

The question should be why doesn't it give an error message before aborting.
What happens if you run it from something like VB.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-21 : 18:52:45

Very interesting.. I will have to digest this..

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-21 : 18:59:35

lol

In SQL Server 2000, if a COMMIT TRANSACTION or COMMIT WORK statement is executed in a trigger, and there is no corresponding explicit or implicit BEGIN TRANSACTION statement at the start of the trigger, users may see different behavior than on SQL Server version 7.0. Placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger is not recommended.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-21 : 19:13:30

Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection

cn.Execute "insert into t select 5; insert into t select 6"

MsgBox cn.Errors.Count

0 errors!!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-21 : 19:44:21

nr,
(I overlooked your last post + I'm testing it on sql 7.0),
really lol... btw this inserts two rows (with the same trigger):

begin tran
insert into t select 5
insert into t select 6
commit tran

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-21 : 20:06:08

Doesn't on mine. (sp3)

needs 2 begin trans

begin tran
begin tran
insert into t select 5
insert into t select 6
commit tran
commit tran

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-21 : 20:24:28

When it exits the trigger with a 0 trancount it executes no more statements. The commit in the trigger does decrement the trancount and will commit if it reaches 0. When it reaches 0 you also lose the inserted table entry.

create trigger tr on t
for insert
as
select beforecommitintrigger = @@trancount, (select * from inserted)
commit tran
select aftercommitintrigger = @@trancount, (select * from inserted)
go

begin tran
begin tran
select beforeinsert1 = @@trancount
insert into t select 5
select afterinsert1 = @@trancount
commit tran
insert into t select 6
select afterinsert2 = @@trancount
select afterinsert2 = @@trancount
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-22 : 06:47:34

Thanks, Nigel, for explanations.. BTW, they provide a workaround for
the following question (I met it recently on an other forum):

how to prevent terminating of a batch if in the 'middle' of it in some
fired trigger was met condition for ROLLING BACK?
Workaround in bold:

create table t (n int primary key)
go
create trigger tr on t
for insert
as
if (select * from inserted)=15 begin rollback tran begin tran end
go

insert into t select 5
insert into t select 6
insert into t select 15 -- << bad value
insert into t select 888
insert into t select 9999
go

select * from t
drop trigger tr
drop table t
go

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-22 : 07:20:58

That comes under "things not to do".
Think about what will happen if someone decides to put a transaction round the inserts.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-22 : 16:07:32

Yes.. it is dangerous workaround..

But interesting to play around with..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources