How ignore “The transaction ended in the trigger. The batch has been aborted.” in SQL

by

My initial goal is to have an automatic backup file created for every routine before it is deleted.

To do this, I created the following trigger at the server level.

ALTER TRIGGER [trg_DDL_BackupOfRoutines]
ON ALL SERVER
FOR DROP_TRIGGER
    , DROP_VIEW
    , DROP_FUNCTION
    , DROP_PROCEDURE
AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    -- prevent kill transaction when raiserror is fired
    DECLARE @XACT_ABORT_WasOn BIT = 0
    IF (@@OPTIONS & 16384) = 16384
        BEGIN
            SET @XACT_ABORT_WasOn = 1
            SET XACT_ABORT OFF;
        END

    DECLARE @Event XML = EVENTDATA()

    DECLARE @DatabaseName NVARCHAR(100)
            , @SchemaName NVARCHAR(50)
            , @ObjectName NVARCHAR(100)
            , @ObjectType NVARCHAR(50)

BEGIN TRY

    ----------------------------------------------------------------------------------------------------
    -- Getting event data

    SELECT @DatabaseName = @Event.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(100)')
    SELECT @SchemaName = @Event.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(50)')
    SELECT @ObjectName = @Event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)')
    SELECT @ObjectType = @Event.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(50)')

    DECLARE @ObjectFullName VARCHAR(255)
    
    SET @ObjectFullName = CONCAT(@SchemaName, '.', @ObjectName)
    
    ----------------------------------------------------------------------------------------------------
    -- validate if exists file

    DECLARE @Path VARCHAR(255)
            , @Date VARCHAR(10)
            , @FileName VARCHAR(500)

    SET @Path = CONCAT('L:\ObsoleteObjects\', @DatabaseName, '\')
    SET @FileName = CONCAT(@Path, @SchemaName, '.bak_', @ObjectName, '_', FORMAT(GETDATE(), 'yyyyMMdd'), '.sql')


    DECLARE @FileInfo TABLE (
        FileExists BIT
        , FileIsADirectory BIT
        , ParentDirectoryExists BIT
    )

    INSERT INTO @FileInfo
    EXEC master.dbo.xp_fileexist  @FileName

    IF EXISTS (SELECT * FROM @FileInfo WHERE FileExists = 1)
        BEGIN
            GOTO Finish
        END
    ELSE
        BEGIN
            ROLLBACK
        END
    
    ----------------------------------------------------------------------------------------------------
    -- validate if folder exists

    IF EXISTS (SELECT * FROM @FileInfo WHERE ParentDirectoryExists = 0)
        BEGIN
            
            DECLARE @QueryFolder VARCHAR(1000)
            SET @QueryFolder="MD " + @Path

            EXEC xp_cmdshell @QueryFolder

        END
    
    ----------------------------------------------------------------------------------------------------
    -- Get object definition

    DECLARE @DefinitionQuery NVARCHAR(MAX)
            , @Definition NVARCHAR(MAX)

    SET @DefinitionQuery = CONCAT('USE [', @DatabaseName, ']

    SELECT @Definition = OBJECT_DEFINITION(OBJECT_ID(', CHAR(39), @ObjectFullName, CHAR(39), '))
    ')
            
    EXEC sp_executesql @DefinitionQuery
        , N'@Definition NVARCHAR(MAX) OUTPUT'
        , @Definition = @Definition OUTPUT
    
    ----------------------------------------------------------------------------------------------------
    -- Generate backup file

    DECLARE @OLE            INT 
    DECLARE @FileID         INT

    EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 
    EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @FileName, 8, 1 
    EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Definition
    EXECUTE sp_OADestroy @FileID 
    EXECUTE sp_OADestroy @OLE 
    
    ----------------------------------------------------------------------------------------------------
    -- Continue with the drop

    DECLARE @DropQuery NVARCHAR(MAX)

    SET @DropQuery = CONCAT('USE [', @DatabaseName, ']
    
    DROP ', @ObjectType, '[', @SchemaName, '].[', @ObjectName, ']')

    EXEC (@DropQuery)
    
    ----------------------------------------------------------------------------------------------------
    -- Define output message

    DECLARE @OutputMessage NVARCHAR(MAX) = CONCAT('
The object has been removed.

A backup of file was automatically generated, can you find it in the following path: ', @FileName, '

Please ignore the message:

"The transaction ended in the trigger. The batch has been aborted."

The error message is generated because the initial "DROP" statement is canceled to get the object definition and then the "DROP" statement is executed again.
    ')

    PRINT @OutputMessage

Finish:

    ----------------------------------------------------------------------------------------------------
    -- ROLLBACK OPTION TO ORIGINAL STATE
    IF (@XACT_ABORT_WasOn = 1)
        BEGIN
            SET XACT_ABORT ON;
        END

END TRY
BEGIN CATCH
    ROLLBACK

    DECLARE @Error NVARCHAR(MAX) = ERROR_MESSAGE()
    
    ----------------------------------------------------------------------------------------------------
    -- ROLLBACK OPTION TO ORIGINAL STATE
    IF (@XACT_ABORT_WasOn = 1)
        BEGIN
            SET XACT_ABORT ON;
        END
    
    ----------------------------------------------------------------------------------------------------
    -- Show error
    RAISERROR(@Error, 16, 1)

END CATCH

END
GO

The code works well and creates the backup file automatically, the only annoying thing is that it marks that the transaction was aborted in the trigger.

Msg 3609, Level 16, State 2, Line 10
The transaction ended in the trigger. The batch has been aborted.

Is there a way to not show that message?

  • 2

    By not doing things the way you are. Don’t ROLLBACK in the TRIGGER, instead THROW the appropriate error (with a relevant severity); let the outer scope handle the rollback.

    – 




  • I rollback the transaction because I didn’t find another way to get the definition of the object, otherwise it won’t find the object.

    – 

  • 1

    How does ROLLBACK give you th definition of an object?

    – 

  • I think the reason why it does not find the definition is because the server event is executed a little after the DROP, by the time I do the ROLLBACK the definition is already available again.

    – 

  • 2

    @ThomA Careful reading of the (rather poor) code indicates that OP is rolling back the DROP in order to grab the definition and save it to a file, then redo the DROP. So they need to roll back the transaction but prevent the trigger/rollback error. The solution might be to start a new transaction dbfiddle.uk/gRjY-jX4 although the real answer is to use source control and backups.

    – 

I’ll preface this by saying: this is absolutely the wrong solution for what you are trying to do. You should use a proper source control and backup system (backups include all object definitions).

The other problem with your use of a trigger and a rollback is that anyone doing DDL inside their own outer transaction is going to be in for a nasty surprise when you roll it back.

Your primary issue is that you are ending the transaction but not starting a new one. The system expects that there is still an active transaction at the end of the trigger, but you need to roll it back in order to get the definition. So instead start a new transaction. And you don’t need to mess around with catching any exceptions then.

Also:

  • You can get the XML data in a single query. You should use /text() for better performance.
  • Use the newer sys.dm_os_file_exists function if you need it.
  • Ideally don’t write to the file system at all from T-SQL, instead just write to a table.
  • You don’t need USE in the dynamic SQL. You can instead put CONCAT(@DatabaseName, '.sys.sp_executesql') into a variable and do EXEC @proc.
  • To redo the DROP just pull out the original command.
  • Use sys.sql_modules instead of OBJECT_DEFINTIION for better reliability and locking.
  • Object names should be in sysname type (a synonym for nvarchar(128)) and file names should be in nvarchar(260).
CREATE OR ALTER TRIGGER [trg_DDL_BackupOfRoutines]
ON ALL SERVER
FOR DROP_TRIGGER
    , DROP_VIEW
    , DROP_FUNCTION
    , DROP_PROCEDURE
AS

SET NOCOUNT ON;

-- prevent recursion
IF @@TRIGGER_NESTLEVEL(OBJECT_ID('trg_DDL_BackupOfRoutines', 'AFTER', 'DDL')) > 1
    RETURN;
  
DECLARE @Event XML = EVENTDATA();

DECLARE @DatabaseName sysname
      , @SchemaName sysname
      , @ObjectName sysname
      , @Command nvarchar(max);

SELECT
  @DatabaseName = EventInstance.value('(DatabaseName/text())[1]', 'sysname'),
  @SchemaName = EventInstance.value('(SchemaName/text())[1]', 'sysname'),
  @ObjectName = EventInstance.value('(ObjectName/text())[1]', 'sysname'),
  @ObjectType = EventInstance.value('(ObjectType/text())[1]', 'NVARCHAR(50)'),
  @Command = EventInstance.valu('(TSQLCommand/CommandText/text())[1]', 'nvarchar(max)')
FROM @Event.nodes('/EVENT_INSTANCE') x1(EventInstance);
    ----------------------------------------------------------------------------------------------------
-- Get object definition

ROLLBACK;

DECLARE @proc NVARCHAR(1000) = CONCAT(@DatabaseName, '.sys.sp_executesql');

EXEC @proc
    N'
INSERT master.sys.DefinitionBackup
  (SchemaName, ObjectName, Definition)
SELECT @SchemaName, @ObjectName, m.definition
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE s.name = @SchemaName
  AND o.name = @ObjectName;
'
  , N'@ObjectName sysname,
      @SchemaName sysname',
  , @ObjectName = @ObjectName
  , @SchemaName = @SchemaName;

----------------------------------------------------------------------------------------------------
-- Begin a new transaction and continue with the drop

BEGIN TRAN;

EXEC @proc @Command;

----------------------------------------------------------------------------------------------------
-- Define output message

DECLARE @OutputMessage NVARCHAR(MAX) = CONCAT('
The object has been removed.

A backup of file was automatically generated, can you find it in the following path: ', @FileName, '

Please ignore the message:

"The transaction ended in the trigger. The batch has been aborted."

The error message is generated because the initial "DROP" statement is canceled to get the object definition and then the "DROP" statement is executed again.
    ');
PRINT @OutputMessage;

Leave a Comment