Rami Vemula

Prior to reading this article, please learn how Nested Transactions works.

There are at times when you want to your trigger to do some specific job for you like cleaning up things, archiving data, auditing certain objects, validating related data etc. Specifically at the former mentioned scenarios you would be requiring transactions to take care of multiple operations including the operation which fired up the trigger. So its important to know how transactions would work with triggers.

Using below code, I tried to narrate different combinations of transactions including nesting some of them. It will be clearly visible (understandable) with the below example which data will be committed and which data will not. Code is very much self explanatory with a stored procedure to insert some data into two different tables, and on insert into TestTable, the corresponding insert trigger will fire up and will do some insert operations. To explain Rollback scenarios, I specifically made a rollback in trigger, so that we can understand what would happen in rollback scenarios. Commit scenarios are mostly straight forward, so my main focus is specifically in rollback scenarios.

I have comments in the code which will why it is expected result.

USE [TestDB]
GO

/* Drop and Recreate Table */

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'TestTable')
BEGIN
    DROP TABLE [dbo].[TestTable];
END
GO

CREATE TABLE [dbo].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
)
GO

/* Drop and Recreate a Table for Trigger to insert archieve data */

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'Sample')
BEGIN
    DROP TABLE [dbo].[Sample];
END
GO

CREATE TABLE [dbo].[Sample](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
)
GO

/* Drop and Recreate Trigger */

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'TR' AND name = 'TestTableInsertTrigger')
BEGIN
    DROP TRIGGER [dbo].[TestTableInsertTrigger];
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TestTableInsertTrigger]
   ON  [dbo].[TestTable]
   AFTER INSERT
AS
BEGIN

    /* This Insert doesn't persist because of the immediate Rollback */
    INSERT INTO [dbo].[Sample](Name) VALUES('Doesn''t Persists 4'); -- Doesn't Persists
    ROLLBACK TRAN;

    /* This Insert persist because this stratement transaction is not incolved in the Trigger
       Rollback. And it never belongs to any transaction. */
    INSERT INTO [dbo].[Sample](Name) VALUES('Persists 5'); -- Persists

END
GO

/* Drop and Recreate Stored Procedure */

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'P' AND name = 'TestTableInsertProcedure')
BEGIN
    DROP PROCEDURE [dbo].[TestTableInsertProcedure];
END
GO

CREATE PROCEDURE [dbo].[TestTableInsertProcedure]
AS
BEGIN

    SET NOCOUNT ON;

    BEGIN TRAN
    /* This Insert persist because this stratement transaction is not incolved in the Trigger
       Rollback. And it is altogether in its own transaction. */
    INSERT INTO [dbo].[Sample](Name) VALUES('Persists 1');
    COMMIT TRAN

    BEGIN TRAN T1

    /* This Insert doesn't persist because the transaction is rollback in Trigger which is caused
       by next insert statement. And in nested transactions using Rollback will rollback the parent
       transaction along with all other inner transactions. */
    INSERT INTO [dbo].[Sample](Name) VALUES('Doesn''t Persists 2'); -- Doesn't Persists

    BEGIN TRAN

    BEGIN TRY
        /* This Insert make the trigger fire. Trigger is always implicitly part of the same
           transaction as the statement that caused the trigger to fire. So the Rollback issued
           in the trigger will rollback the parent transaction (because these transactions are
           nested) of this stored procedure.  */
        INSERT INTO [dbo].[TestTable](Name) VALUES('Doesn''t Persists 3') -- Doesn't Persists
    END TRY

    BEGIN CATCH
        IF (SELECT ERROR_NUMBER()) IN (3609,3616)
            SELECT 'Something wrong happened' AS ERROR;
        ELSE
            SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;

            IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRAN;
            END
    END CATCH

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRAN;
        COMMIT TRAN T1;
    END

    /* This Insert persist because this stratement transaction is not incolved in the Trigger
       Rollback. And it never belongs to any transaction. */
    INSERT INTO [dbo].[Sample](Name) VALUES('Persists 6'); -- Persists

END
GO

/* Execute Stored Procedure */

EXEC [dbo].[TestTableInsertProcedure]
GO

/* Select Statements to check results */
SELECT * FROM [dbo].[TestTable];
SELECT * FROM [dbo].[Sample];

And the output would be –