CODEWITHSUNDEEP
sqlstored-procedures
Ben Atlas
Ben Atlas

Reputation: 127

Revert SQL Operation Over Multiple Stored Procedures

We are working on an application where, sometimes, we need to delete (some) entries from an SQL table and replace them with a new list of rows. In our app, the logic goes:

Delete relevant_entries using delete_stored_procedure

For Each new_entry in new_entries
  Insert new_entry using insert_stored_procedure

This logic works, but there is a catch. What if the DELETE works, but one of the INSERTS fail? We need to be able to revert the database back to before the table rows were deleted and any previous entries were inserted. Is there a way to do this and ONLY revert these things, without affecting any other unrelated operations that may have happened simultaneously? (i.e., a row gets created in another table- we don't want to revert that).

I understand that you can wrap multiple SQL statements in a transaction, but in this case, the logic is happening over multiple stored procedure calls. Any advice?

Upvotes: 0

Views: 65

Answers (1)

tpdi
tpdi

Reputation: 35141

Create another stored procedure that:

  • creates a transaction
  • calls the stored procedures that do the work
  • commits or rolls back the transaction

Upvotes: 3

Related Questions