Can I change table structure in a transaction and then roll it back if there is an error?
Yes, this is possible.
Most DDL statements can be rolled back in SQL Server (There are a few exceptions such as CREATE DATABASE
)
BEGIN TRANSACTION
BEGIN TRY
ALTER TABLE1...
ALTER TABLE2...
-- Additional data/structural changes
COMMIT
END TRY
BEGIN CATCH
ROLLBACK;
THROW; -- Only if you want reraise an exception (to determine the reason of the exception)
END CATCH
Yes, it is possible to use many ALTER TABLE
statements in one transaction with ROLLBACK
and COMMIT
.
Here is a scaffold for your script (following MS guidelines with improvements):
BEGIN TRANSACTION
BEGIN TRY
-- place your script in this TRY block
-- your DDL instructions:
ALTER TABLE1...
ALTER TABLE2...
-- data modifications:
EXEC('
UPDATE A
SET c1 = 23,
c2 = ''ZZXX'';
');
-- another DDL instruction:
ALTER TABLE2...
-- end of your script
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- If you want reraise an exception (to determine the reason of the exception)
-- just uncomment block with appropriate version:
-- SQL SERVER >= 2012
/*
THROW;
*/
-- SQL SERVER < 2012 (tested against 2008 R2)
/*
DECLARE @ErrorMessage VARCHAR(MAX);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
*/
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
Be careful, THROW
works only for SQL SERVER version >= 2012. Here you could convert a version from semver to year notation: http://sqlserverbuilds.blogspot.ru (do not aware of .ru
domain, there is an English version)