[per4711]

We have discovered some wierd behavior. Please read the the example procedure and example trigger before I explain the problem.

Procedure X does this:
...
open crsr
fetch crsr into @number
while(@@sqlstatus = 0)
begin
insert into tableY(number) values(@number)
....
fetch crsr into @number
end
deallocate cursor crsr
...

Inserttrigger on table tableY:
create trigger trg_test on tableY for insert as
declare
@id numeric(10,0)
begin transaction
select @id = id from inserted
select @number = isnull(max(number), 0) + 1 from tableY
update tableY set number = @number where id = @id
commit

The Problem
For some reason we ended up with several rows in tableY with the identical value in the column number.

Of cource we could avoid this by adding an unique constraint but I would really like to know how this happend. And I also found the same error in another column where an constraint is not an option.

We use "Lock scheme Datarows" on tableY.

During the execution of procedure X we detected the error "The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.". This error has never been seen before and procedure X has not been modified (and we have not changed the option "ddl in tran").

Does anyone have a clue what this is?

I would appreciate a solution very much since we have spent almost a week to cleen everything up after this error.

Thank you!