How to rollback each and every insertion if there is any validation is missing

Question

I have to insert data from xlm to 10 tables, the hierarchy is as below.

• T1 has child table T2
• T2 have child tables t3,t4,t5,t6 etc till t10
• If any of the validation fails in t3 to t10 above t1, t2 tables insertion should rollback.

I am using SavePoint and if is see any validation missing i will rollback that particular savepoint.

My question here is, do i have to use 10 savepoint so roll back the transaction of all tables or only one savepoint is enough?

Answer 1

If any of the validation fails in t3 to t10 above t1, t2 tables insertion should rollback.

So you mean if anything goes wrong while inserting into T3..T10, you want the entire transaction to rollback to initial point, i.e. any inserts into T1 and T2 should also rollback.

Then why to create a savepoint at all. Simply issue a ROLLBACK, it will rollback all the inserts, the entire transaction.

Anyway, if you really need, then create a SAVEPOINT first, before starting any transaction on T1. Now, if any validation fails, ROLLBACK TO SAVEPOINT, which will rollback the entire transaction.

ROLLBACK TO SAVEPOINT means any transaction that happened after that particular SAVEPOINT will be rolled back till that savepoint.

For example,

SQL> SAVEPOINT A    

SQL> INSERT INTO TEST VALUES (1,'Savepoint A');

1 row inserted.    

SQL> SAVEPOINT B    

SQL> INSERT INTO TEST VALUES (2,'Savepoint B');

1 row inserted.    

SQL> ROLLBACK TO B;    

Rollback complete.    

SQL> SELECT * FROM TEST;    

ID  MSG  
--------    -----------   
1           Savepoint A

Example source