Error in transaction -> no rollback!
Posted on 2001-08-15
I have the following stored procedure: (ASE 11.9 on AEX)
create procedure test1
insert into testtable1 values (1)
insert into testtable1 values (2)
insert into testtable1 values (2) <-- key violation
insert into testtable1 values (3)
If I define the field as primary key, statement 3 will fail off course. But the transaction is not aborted nor rollbacked! In other words: afterwards my table will contain:
Off course, this is not what I want. Checking @@rowcount is not an option, since I also have update statements (It's possible that the statement updates 0 rows without errors)
I want my transaction to do everything succesfull OR nothing at all. Any suggestions?