| Delete, Insert and rollback to before delete if insert fails [message #122067] |
Thu, 02 June 2005 12:33  |
Ricky_newbie
Messages: 12
Registered: May 2005
|
Junior Member |
|
|
All,
I have a problem here with writing program.
I have a delete statement and inserting records into the same table. I have a situation where insert fails with Duplicate values. But for now what i need is if there is any way, i can program to rollback to before delete statement and goto next step. This is my example;
delete from emp where deptno = '10'
insert into emp(deptno)
select deptno from dept
If insert fails here
Rollback to before delete
commit;
else commit;
and goto next step
Help needed please.
Thanks,
Rick
|
|
|
|
|
|
|
|
|
|
| Re: Delete, Insert and rollback to before delete if insert fails [message #122119 is a reply to message #122080] |
Fri, 03 June 2005 00:34  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
Well in first reply I mistook that you want to commit after delete in case insertion fails.
If you want to rollback all your work if insertion fails after deletion, u can simply use rollback.
Do clarify me if I am wrong in understanding scenario:
U want the following taks:
0. Start of transaction
1. delete from emp where deptno = '10'
2. insert into emp(deptno) select deptno from dept
3. commit if no error else
when error rollback to 0.
If this is the case, simply check for exception and issue rollback in the when error clause at step 3.
But in case u want to rollback upto stage after step 1, then use savepoint there before step 2 and after step 1.
Declare it like savepoint s.
And then in when error clause give rollback to s & then commit.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
