I have AutoCommit set to OFF (as shown by the icon in the bottom-left of the screen) and I do not have the AutoCommit checkbox set in Tools->Options->Database->General (Commit after each statement in Editor mode).
If I issue an UPDATE command, I can see the changes with a corresponding SELECT statement. If I then type in ROLLBACK, the messages pane says that 1 row was affected, but the data has been committed nonetheless (as evidenced by the same SELECT statement). Closing and re-opening Toad has no effect. The data has still been updated.
Could someone please verify this before I enter an SR ticket? This is a potentially dangerous oversight.
Addendum: When working from PowerBuilder, the same thing happens. However, when using SQL Central, the UPDATE followed by a ROLLBACK works as expected. Not sure where the issue is because both programs do not have autocommit set.
Further testing has shown that if the insert/update statement is in the same begin/end block as the rollback, the rollback does indeed work. It’s almost as if each statement in the editor creates its own transaction when executed. Actually, it’s more like each highlighted block of statements are executed in their own transaction. For example, if I type an UPDATE statement and highlight it and run it THEN type in a ROLLBACK and highlight it and run it, the rollback fails to do anything even though it reported ‘1 row affected’. If, however, I type in the update and the rollback statements and highlight them BOTH, the rollback does work.
This issue has been reported and we are going to address it in next release. We would notify you once this issue is fixed. Thank you for your patience.