Rollback command in SQL

5 pts.
Suppose user x insert values into table t1,and then update the table t1. After then he wants to increase the size of column c1. Then he wants to rollback to update values. He can do this or not? If yes then what is the right procedure?

Software/Hardware used:

Answer Wiki

Thanks. We'll let you know when a new response is added.

With most databases you begin a transaction, and you can rollback the entire transaction, or commit the entire transaction. You can’t rollback specific statements within the batch.




At this point you can either COMMIT the transaction or ROLLBACK the transaction. You can’t do just a single statement within the transaction.


You didn’t specify what database you are using, and I think the answer could be different depending on that.

In Oracle, for example, there is the SAVEPOINT command, which marks the current point in the processing of a transaction. It can be used in conjunction with the command ROLLBACK TO <savepoint_name> to undo <b>parts</b> of a transaction.

Also, Oracle has an AUTONOMOUS TRANSACTION feature, which lets you create a stored procedure or function that will be managed separately as an autonomous transaction (i.e. it can be committed or rolled back without the need to commit or roll back the other statements that are part of the transaction from which it was called).

As for altering a table/column inside a transaction, Oracle issues an implicit COMMIT when a DDL command is executed, thus if you execute some DML commands and then you execute a DDL command, your previous operations are automatically committed and you can’t roll them back.

I would add one thing to the above answer…
ROLLBACK does away with changed data back to the beginning of the logical unit of work.
A logical unit of work is defined as “from the beginning of the program (transaction) OR to the last commit point”

If a program issues multiple COMMIT statements, ROLLBACK will return the data to the state of the last commit. But remember, you cannot go back wards beyond the last commit. Further, one does not want to issue COMMITs more often than required because they are rather expensive.


Concerning the Oracle implicit COMMIT on DDL; to work around this issue use to blocks:





WHEN e_myException
END; --end DML block


WHEN e_DDLException
END; --end DDL block


The problem with the original question is that he isn’t asking about how to roll back from a column width increase error – he is asking how to roll back to the previous state after the DDL change succeeds. I am unaware of any database that has a roll back to undo a successful DDL action.

If I had to provide a user with this kind of capability, my approach would be to capture the table prior to the DDL, and restore the table later if needed. In Oracle, capturing the table is simply:
CREATE TABLE <newtable> AS SELECT * FROM <oldtable>
This creates a new table with the same structure as the old one, populated with the data.

There is no equivalent in SQL Server – you need to write some interesting dynamic T-SQL code or use the Management Studio tools to script the table construction and data copy.

Obviously, you also need to capture the indexes, constraints, etc. and restore those as well.

Overall – I am having trouble understanding the purpose of the question…

” I am unaware of any database that has a roll back to undo a successful DDL action.”
DB2 does not auto-commit ANY SQL. If you issue an ALTER, you may then follow it with a ROLLBACK and it will be rolled back.


Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Sql Server doesn't have the implicit commit on DDLs either (I tested it on 2008).
    77,040 pointsBadges:
  • Denny Cherry
    SQL Server also has the save points and labels which can be rolled back to. Based on the brevity of the question, I assumed such an in-depth answer wasn't necessary. Granted it would be nice to know what platform the sub is talking about. Carlos is correct, SQL Server (for as far back as I can remember) doesn't to an implicit commit on DDL allowing you to rollback a successful DDL operation later on if needed. If you have SSMS or Enterprise Manager script out DDL such as adding a column to the middle of a table, the code which is generated counts on the ability to rollback DDL changes.
    67,435 pointsBadges:
  • Kccrosser
    Interesting about SQL DDL not doing an implicit commit. However, I have always understood that any DDL changes are not available to further SQL transactions until the commit occurs. Is that correct? I.e., if I issue an ALTER TABLE statement, I cannot then do any SQL transactions that reference the new column (or any changes resulting from the alter?) until that DDL statement is committed. So - the only value to rolling back a DDL statement is to recover from an error that occurred during the processing of the DDL command itself. Thus, I will restate my understanding more clearly: I am unaware of any database that will allow you to roll back a transaction to before a DDL command where that DDL command was successful AND any transactions were run that used the result of the DDL command.
    3,830 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: