Catching QUOTED_IDENTIFIER Errors in Trigger without aborting transaction

2 votes
0 answers
327 views
sql-server trigger error-handling quoted-identifier
                          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'{Type}{Name}{Value}';
    		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'{Type}{Name}{Value}';
    		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
    UID1UID2
    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 

                        
Asked by Cade Roux (6684 rep)
Aug 1, 2019, 06:08 PM
Catching QUOTED_IDENTIFIER Errors in Trigger without aborting transaction

Related Questions
