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.
INSERT INTO T1...
ALTER TABLE T1
MODIFY C1 ...
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:
CREATE OR REPLACE PROCEDURE myPROD()
INSERT INTO T1...
END; --end DML block
ALTER TABLE T1
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.