0

Below is part of my code that dynamically creates Trigger. The problem I am facing here is when I run this scrip using Agent Job, the QUOTED_IDENTIFIER within the trigger is set to OFF by default. But when I run it by myself as adhoc query, the trigger ends up with SET QUOTED_IDENTIFIER ON option. That's the desired behavior. 

            SET @cmd3 = N'EXEC ' + @dbName + '..sp_executesql N''
                    CREATE TRIGGER TRG_LogAudit ON DATABASE
                        FOR 
                            -- Procedures
                            CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,

                            -- Tables
                            CREATE_TABLE, ALTER_TABLE, DROP_TABLE,

                            -- Views
                            CREATE_VIEW, ALTER_VIEW, DROP_VIEW,

                            -- Functions
                            CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,

                            -- Schemas
                            CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,

                            -- Triggers
                            CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,

                            -- Others
                            RENAME
                    AS
                        BEGIN
                            SET NOCOUNT ON;
                            DECLARE @EventData XML = EVENTDATA();

                            DECLARE @ip VARCHAR(32) = ( SELECT  client_net_address
                                                        FROM    sys.dm_exec_connections
                                                        WHERE   session_id = @@SPID
                                                      );

                            INSERT  meta.LogAudit
                                    (EventType
                                    ,EventDDL
                                    ,EventXML
                                    ,DatabaseName
                                    ,SchemaName
                                    ,ObjectName
                                    ,HostName
                                    ,IPAddress
                                    ,ProgramName
                                    ,LoginName
                                    )
                                    SELECT  @EventData.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''NVARCHAR(100)'''')
                                           ,@EventData.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''NVARCHAR(MAX)'''')
                                           ,@EventData
                                           ,DB_NAME()
                                           ,@EventData.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', ''''NVARCHAR(255)'''')
                                           ,@EventData.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''NVARCHAR(255)'''')
                                           ,HOST_NAME()
                                           ,@ip
                                           ,PROGRAM_NAME()
                                           ,SUSER_SNAME();
                        END;
    ''';
....

How to modify my script in order to make sure trigger would always have QUOTED_IDENTIFIER option set to ON? I have tried some nested EXEC commands but with not success.

DNac
  • 2,663
  • 8
  • 31
  • 54
  • Have you tried just adding `SET QUOTED_IDENTIFIER ON` where needed in the script? – Dan Field Jan 30 '17 at 13:11
  • Yes. But the problem here is that the "CREATE TRIGGER" must be first statement in the query so I cannot just put it there. – DNac Jan 30 '17 at 13:32

0 Answers0