Transaction handling

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.


Didn't find what you were looking for?