use of rollback before making changes in a sql table


The rollback is to undo change in a table when a deletion or update is done, my question is How is its use for an update that I want to do?

asked by ARG RG 29.08.2016 в 16:34

1 answer


In transactional databases, the ROLLBACK is used to discard the modifications made within a TRANSACTION (transaction).

For example, on the command line:

   UPDATE t1 SET col1 = col1 + 1, col2 = col1 WHERE...;   -- fuera de transac
   BEGIN;                                        -- abre transaction
   UPDATE t2 SET col1 = 3 WHERE... ;             -- dentro de transaction
   DELETE FROM t3 WHERE... ;                     -- 
   ROLLBACK;                                     -- deshace cambios desde BEGIN

In this example, the transaction opens with BEGIN (depending on your database and your client API that may vary). The modifications that follow will be part of that transaction, are thought of as "tentative" modifications that will only be "really" recorded (and, typically, will be visible from other connections) when the transaction is confirmed with a COMMIT . If a ROLLBACK is executed instead of the COMMIT, then all the modifications will be undone.

In the example, the first UPDATE t1... is executed outside the transaction, so it is not possible to undo its effects with a ROLLBACK.

This is just a basic explanation, for more details (in reference to SQL-server) you can read the documentation here

answered by 29.08.2016 в 16:59