Menu
I have multiple updates and insert statements in a procedure.
Please refer below example:
Procedure Example
--code
Update 1
insert 1
Update 2
Update 3 --Suppose exception occurs
Now i want to rollback to before 1st update statement means no update or insert affects.
735
A SAVEPOINT is a point in a transaction in which you can roll the transaction back to a certain point without rolling back the entire transaction. Syntax for Savepoint command: SAVEPOINT SAVEPOINT_NAME; This command is used only in the creation of SAVEPOINT among all the transactions.
A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINTs to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.
You can reuse savepoint names within a transaction. The savepoint moves from its old position to the current point in the transaction. If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINT statement are executed at each level in the recursive descent.
After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.
BEGIN
Savepoint do_update_1;
Update 1;
insert 1;
Update 2;
Update 3; --Suppose exception occurs
EXCEPTION
WHEN some_exception THEN Rollback To do_update_1;
END;
====== edit ==========
Working example: http://sqlfiddle.com/#!4/b94a93/1
create table tttt(
id int,
val int
)
/
declare
x int := 0;
begin
insert into tttt values( 1,1);
insert into tttt values( 2,2);
Savepoint do_update_1;
insert into tttt values( 3,3);
update tttt set val = 0 where id = 2;
update tttt set val = 10 / val where id = 2;
exception
when zero_divide then rollback to do_update_1;
end;
/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
