need urgent info regarding rollback

[yogeeswar]

I need to write a SP to decommission one of the value from all tables.but all the deletions need to be made at a time, if problems occurs at any delete statement all the previously deleted rows need to be ROLLBACK. Is there any way fro this.

Thanks in advance

[Herb]

Wrap everything in a BEGIN ATOMIC, that will cause the rollback to rollback every thing. Create a SQL EXCEPTION hander and in that do your rollback. any SQL inside the BEGIN ATOMIC will get rolled back