[EMAIL PROTECTED] wrote:
It appears that my requirements are to be able to do the following:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child; // child aborts
insert into t values ('d');
COMMIT parent;
As a result of this sequence, the table should have two new rows with values
'a' and ‘d', but not 'b' and ‘c’.
I expanded Igor's example because a single transaction failure performs a
rollback. ( Think that is true).
Anyway, the above result is what is required and I cannot do this with the all
or none concept.
Ray,
Can you explain why your application is rolling back the child transaction?
If the above is really how your application works (and I don't think it
is), then the exact same result can always be achieved with the simpler
sequence:
BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;
You don't need to bother inserting b and c if you are going to undo
those insertions with a static rollback.
I see that other databases have the concept of SavePoint.
I believe this will work for me but I am not sure.
BEGIN
insert into t values ('a');
savepoint = SetSavePoint();
insert into t values ('b');
insert into t values ('c');
RestoreSavePoint(savepoint);
insert into t values ('d');
Commit
As a result of this sequence, the table should have two new rows with values
'a' and ‘d', but not 'b' and ‘c’.
The difference here is that there is only one Begin and Commit.
There can be any number of savepoints. This seems to perform the same function.
The standard SQL syntax for this sequence would be:
BEGIN;
insert into t values ('a');
SAVEPOINT after_a;
insert into t values ('b');
insert into t values ('c');
ROLLBACK TO SAVEPOINT after_a;
insert into t values ('d');
COMMIT;
This still begs the question "why are you rolling back to the savepoint"?
These samples are very much out of context in that they don't show how
or why the application is issuing this sequence of SQL statements.
Without that context information its nearly impossible decide if it must
be done this way or not.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
