I have a delete trigger on table A that deletes from table B. I am testing what happens when trigger fails, and for that purpose I renamed table B, so that trigger cannot find it.
These are my normal steps:
begin transaction
delete from C
delete from A -- this errors for reason mentioned
-- At this point the transaction is automatically rolled-back.
However if I do these steps:
begin transaction
delete from C
delete from B -- this errors for reason mentioned
-- At this point transaction is not rolled back, and I can still commit it.
How come in the first scenario, the transaction is rolling back? Shouldn't it be up to application to call either rollback or commit?
And the whole difference is trigger failing vs. statement failing for same reason, I would expect the behavior to be identical.
edit to add example:
create table A (a int primary key)
create table B (a int primary key)
create table C (a int primary key)
create trigger Atrig on A for delete as delete B from B, deleted where B.a=deleted.a
insert into A values(1)
insert into A values(2)
insert into B values(2)
insert into B values(3)
insert into C values(1)
insert into C values(2)
insert into C values(3)
Now rename table B to B2 (I used UI to rename it, so don't have sql command to do so)
begin transaction
delete C where a=3
delete A where a = 2
Above returns this error, and rolls back transaction:
System.Data.OleDb.OleDbException (0x80040E37): [42000]
[Message Class: 16]
[Message State: 1]
[Transaction State: 0]
[Server Name: sybase_15_5_devrs1]
[Procedure Name: Atrig]
[Line Number: 1]
[Native Code: 208]
[ASEOLEDB]B not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
However if I do this:
begin transaction
delete C where a=3
delete B where a = 2
above returns error, but transaction is not rolled back, and I can issue'commit transaction':
System.Data.OleDb.OleDbException (0x80040E37): [42000]
[Message Class: 16]
[Message State: 1]
[Transaction State: 0]
[Server Name: sybase_15_5_devrs1]
[Native Code: 208]
[ASEOLEDB]B not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
I'm thinking the behavior has something to do with this topic In the table "Rollbacks caused by errors in data modification", it says:
Context: Transaction only
Behavior: Current command is aborted. Previous commands are not rolled back, and subsequent commands are executed.
Context: Trigger in a transaction
Behavior: Trigger completes, but trigger effects are rolled back.
All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, the rollback affects all of those batches.
Any remaining commands in the batch are not executed. Processing resumes at the next batch.
Context: Transaction only
Behavior: Current command is aborted. Previous commands are not rolled back, and subsequent commands are executed.
Context: Trigger in a transaction
Behavior: Trigger completes, but trigger effects are rolled back.
All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, the rollback affects all of those batches.
Any remaining commands in the batch are not executed. Processing resumes at the next batch.
After reading the same manual that you specified in the answer this morning, I've tested in my sybase environment and that's what happened.