Rolling Back Data with TCL


  • when working with BEGIN TRANSACTION, you can leverage the ROLLBACK TCL command
    • This command performs an “undo” that allows you to reverse your most recent changes
      • because they have not yet been committed
  • this command will only undo changes you’ve made since the last COMMIT
  • ROLLBACK statement is designed to run in parts, not to be executed all at once
    • lets us confirm the changes we are going to make
    • allows us to confirm they are made
    • then if they are accurate, we can commit them
    • If they are inaccurate, we can roll them back
  • The screenshot below shows our use of ROLLBACK when updating the toy company database:

Example

  • first SELECT pulls all the fields from ToyProducts that have a product name of “Toy Soldeir”
    • This product name was initially misspelled
  • the query will show us the record that needs to be fixed
  • then begin the process with BEGIN TRANSACTION
  • use the UPDATE statement to update the product name with the corrected spelling of Toy Soldier
  • While we are still in the transaction, we can query again to confirm the correction is made
    • If it’s correct, we run COMMIT
    • if not correct, can run ROLLBACK
  • SAVEPOINT allows you to save a certain state to return to later
    • can be coupled with a ROLLBACK
    • allows you to roll back to the SAVEPOINT without rolling back the entire transaction
  • note that you may not always have a SAVEPOINT in your transaction
    • whether or not you do will depend greatly on what you are trying to accomplish with the script and process you are working with