I am coding up a script to merge some accounts (and other tables) as an out-of-band customer request. All the updates will be done in a single transaction. They are all updates (no inserts or deletes) – effectively changing the "account_id" of some entities to achieve the account merge.
That's all fine, but my boss wants to be able to roll back the affected accounts after other transactions, i.e. DAYS down the road on a very active server.
My ideas are: –
- print out all the affected rows (select all the previous data to console) so I could manually move everything back if required (there aren't too many affected tables – about 8)
- export the transaction and "somehow" roll that individual transaction back – without affecting any subsequent transactions.
- something else?
Many thanks!
Best Answer
There's no way to roll back a specific transaction like that using the native tools. There are (or at least there used to be) some third party tools which would let you do this, but it depends on a lot of things.
Your best bet will be to log the before and after values for all the rows that you change during out of band update, then if you need to roll back those changes you've got the needed values. You might want to log the new values as well as the old values so that you can see if the values have been since changed again so that you can then figure out if you want to roll back those changes.