Transaction handling
Published 21 August 2018
By default, Pre & Post-Deployment scripts do not contain any transaction handling and are run outside of any transactions.
A simplified example of the generated deployment script looks like:
Simplified deployment script including Pre & Post-Deployment scripts
SET XACT_ABORT ON GO SET TRANSACTION ISOLATION LEVEL Serializable GO -- The Pre-Deployment script gets inserted here GO BEGIN TRANSACTION GO IF @@ERROR <> 0 SET NOEXEC ON GO PRINT N'Creating [dbo].[myStoredProcedure]' GO CREATE PROC [dbo].[myStoredProcedure] AS SELECT 1 GO IF @@ERROR <> 0 SET NOEXEC ON GO COMMIT TRANSACTION GO IF @@ERROR <> 0 SET NOEXEC ON GO -- The Post-Deployment script gets inserted here GO DECLARE @Success AS BIT SET @Success = 1 SET NOEXEC OFF IF (@Success = 1) PRINT 'The database update succeeded' ELSE BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION PRINT 'The database update failed' END GO
Adding transaction handling to Pre & Post-Deployment scripts
You can write any SQL in Pre & Post-Deployment scripts, including your own transaction handling. For example:
BEGIN TRANSACTION GO IF @@ERROR <> 0 SET NOEXEC ON GO ALTER DATABASE CURRENT SET RECOVERY SIMPLE GO IF @@ERROR <> 0 SET NOEXEC ON GO COMMIT TRANSACTION GO IF @@ERROR <> 0 SET NOEXEC ON GO
If written this way, the deployment is not atomic - the transaction for the Pre-Deployment script may be committed whilst the main transaction is rolled back. If an atomic deployment is required, you can BEGIN
a transaction in the Pre-Deployment script and COMMIT
it in the Post-Deployment script. As a consequence, the main transaction will be nested within this outer transaction.