jx7DQ3c4

1SET NOCOUNT ON;
2GO
3
4IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable')
5BEGIN
6    DROP TABLE dbo.TestTable ;
7END
8GO
9
10CREATE TABLE dbo.TestTable (
11    ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
12    ,Name varchar(50) NOT NULL
13);
14GO
15
16INSERT INTO dbo.TestTable (Name)
17VALUES ('Test 1'), ('Test 2');
18GO
19
20SET QUOTED_IDENTIFIER ON
21GO
22
23CREATE TRIGGER dbo.QuoteTest
24ON dbo.TestTable
25AFTER INSERT, UPDATE, DELETE
26AS
27BEGIN
28    SET NOCOUNT ON;
29    SET XACT_ABORT OFF;
30
31    BEGIN TRY
32        DECLARE @ChangeTemplate nvarchar(max) = N'<Change><Type>{Type}</Type><Key><Column><Name>{Name}</Name><Value>{Value}</Value></Column></Key></Change>';
33        DECLARE @ChangeMsg XML;
34        WITH ChangeRows AS (
35            SELECT [Type] = CASE WHEN inserted.ID IS NULL THEN 'D' WHEN deleted.ID IS NULL THEN 'I' ELSE 'U' END
36                ,[Name] = 'ID'
37                ,[ID] = COALESCE(inserted.[ID], deleted.[ID])
38            FROM inserted
39            FULL OUTER JOIN deleted
40                ON inserted.[ID] = deleted.[ID]
41        )
42        SELECT @ChangeMsg = (SELECT REPLACE(REPLACE(REPLACE(@ChangeTemplate
43                                    , '{Type}', [Type])
44                                    , '{Name}', [Name])
45                                    , '{Value}', [ID])
46                            FROM ChangeRows
47                            ORDER BY ID
48                            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
49        ;
50        PRINT 'Message Built';
51        PRINT CAST(@ChangeMsg AS varchar(max));
52    END TRY
53    BEGIN CATCH
54        PRINT 'Catch Error in Trigger';
55        PRINT ERROR_MESSAGE();
56    END CATCH
57END
58GO
59
60UPDATE TestTable
61SET Name = Name;
62GO
63
64IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TestTable')
65BEGIN
66    DROP TABLE dbo.TestTable ;
67END
68GO
69
70CREATE TABLE dbo.TestTable (
71    ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
72    ,Name varchar(50) NOT NULL
73);
74GO
75
76INSERT INTO dbo.TestTable (Name)
77VALUES ('Test 1'), ('Test 2');
78GO
79
80SET QUOTED_IDENTIFIER OFF
81GO
82
83CREATE TRIGGER dbo.QuoteTest
84ON dbo.TestTable
85AFTER INSERT, UPDATE, DELETE
86AS
87BEGIN
88    SET NOCOUNT ON;
89    SET XACT_ABORT OFF;
90
91    BEGIN TRY
92        DECLARE @ChangeTemplate nvarchar(max) = N'<Change><Type>{Type}</Type><Key><Column><Name>{Name}</Name><Value>{Value}</Value></Column></Key></Change>';
93        DECLARE @ChangeMsg XML;
94        WITH ChangeRows AS (
95            SELECT [Type] = CASE WHEN inserted.ID IS NULL THEN 'D' WHEN deleted.ID IS NULL THEN 'I' ELSE 'U' END
96                ,[Name] = 'ID'
97                ,[ID] = COALESCE(inserted.[ID], deleted.[ID])
98            FROM inserted
99            FULL OUTER JOIN deleted
100                ON inserted.[ID] = deleted.[ID]
101        )
102        SELECT @ChangeMsg = (SELECT REPLACE(REPLACE(REPLACE(@ChangeTemplate
103                                    , '{Type}', [Type])
104                                    , '{Name}', [Name])
105                                    , '{Value}', [ID])
106                            FROM ChangeRows
107                            ORDER BY ID
108                            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
109        ;
110        PRINT 'Message Built';
111        PRINT CAST(@ChangeMsg AS varchar(max));
112    END TRY
113    BEGIN CATCH
114        PRINT 'Catch Error in Trigger';
115        PRINT ERROR_MESSAGE();
116    END CATCH
117END
118GO
119
120UPDATE TestTable
121SET Name = Name;
122GO 
123     
124Message Built
125<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>
126Catch Error in Trigger
127SELECT 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.