Catching QUOTED_IDENTIFIER Errors in Trigger without aborting transaction

Question

Is there a way to catch a QUOTED_IDENTIFIER error without failing the transaction?

I have assembled a minimal test case here. The trigger must have SET QUOTED_IDENTIFIER ON because it uses XML data processing to concatenate the message it builds.

In my particular case, the database where it was installed - the script did not SET QUOTED_IDENTIFIER ON because the trigger was not installed by my normal installation process. I'd like to know if there is a way to trap this without failing the transaction.

In normal operations, I don't want any errors in the trigger to fail transactions, because it is solely designed to construct a message and send it to the service broker for later processing, and should not interfere with operations on the table otherwise.

SET NOCOUNT ON;
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable')
BEGIN
    DROP TABLE dbo.TestTable ;
END
GO

CREATE TABLE dbo.TestTable (
    ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
    ,Name varchar(50) NOT NULL
);
GO

INSERT INTO dbo.TestTable (Name)
VALUES ('Test 1'), ('Test 2');
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.QuoteTest
ON dbo.TestTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    BEGIN TRY
        DECLARE @ChangeTemplate nvarchar(max) = N'<Change><Type>{Type}</Type><Key><Column><Name>{Name}</Name><Value>{Value}</Value></Column></Key></Change>';
        DECLARE @ChangeMsg XML;
        WITH ChangeRows AS (
            SELECT [Type] = CASE WHEN inserted.ID IS NULL THEN 'D' WHEN deleted.ID IS NULL THEN 'I' ELSE 'U' END
                ,[Name] = 'ID'
                ,[ID] = COALESCE(inserted.[ID], deleted.[ID])
            FROM inserted
            FULL OUTER JOIN deleted
                ON inserted.[ID] = deleted.[ID]
        )
        SELECT @ChangeMsg = (SELECT REPLACE(REPLACE(REPLACE(@ChangeTemplate
                                    , '{Type}', [Type])
                                    , '{Name}', [Name])
                                    , '{Value}', [ID])
                            FROM ChangeRows
                            ORDER BY ID
                            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ;
        PRINT 'Message Built';
        PRINT CAST(@ChangeMsg AS varchar(max));
    END TRY
    BEGIN CATCH
        PRINT 'Catch Error in Trigger';
        PRINT ERROR_MESSAGE();
    END CATCH
END
GO

UPDATE TestTable
SET Name = Name;
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable')
BEGIN
    DROP TABLE dbo.TestTable ;
END
GO

CREATE TABLE dbo.TestTable (
    ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
    ,Name varchar(50) NOT NULL
);
GO

INSERT INTO dbo.TestTable (Name)
VALUES ('Test 1'), ('Test 2');
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE TRIGGER dbo.QuoteTest
ON dbo.TestTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    BEGIN TRY
        DECLARE @ChangeTemplate nvarchar(max) = N'<Change><Type>{Type}</Type><Key><Column><Name>{Name}</Name><Value>{Value}</Value></Column></Key></Change>';
        DECLARE @ChangeMsg XML;
        WITH ChangeRows AS (
            SELECT [Type] = CASE WHEN inserted.ID IS NULL THEN 'D' WHEN deleted.ID IS NULL THEN 'I' ELSE 'U' END
                ,[Name] = 'ID'
                ,[ID] = COALESCE(inserted.[ID], deleted.[ID])
            FROM inserted
            FULL OUTER JOIN deleted
                ON inserted.[ID] = deleted.[ID]
        )
        SELECT @ChangeMsg = (SELECT REPLACE(REPLACE(REPLACE(@ChangeTemplate
                                    , '{Type}', [Type])
                                    , '{Name}', [Name])
                                    , '{Value}', [ID])
                            FROM ChangeRows
                            ORDER BY ID
                            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ;
        PRINT 'Message Built';
        PRINT CAST(@ChangeMsg AS varchar(max));
    END TRY
    BEGIN CATCH
        PRINT 'Catch Error in Trigger';
        PRINT ERROR_MESSAGE();
    END CATCH
END
GO

UPDATE TestTable
SET Name = Name;
GO

Gives the following output:

Message Built
<Change><Type>U</Type><Key><Column><Name>ID</Name><Value>1</Value></Column></Key></Change><Change><Type>U</Type><Key><Column><Name>ID</Name><Value>2</Value></Column></Key></Change>
Catch Error in Trigger
SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Reviewed this, but not sure where QUOTED_IDENTIFIER fits in the possible error modes: http://www.sommarskog.se/error_handling/Part2.html

Catching QUOTED_IDENTIFIER Errors in Trigger without aborting transaction

0 Answers0