locked
How to create a trigger from dynamic sql with proper ansi settings RRS feed

  • Question

  • Dear colleagues,

    When we automatically deploy the new version of our database at a customers site, we automatically set aside the customization in a table. After the deployment, we reintroduce the customization. We discovered a problem. Sometimes, customers write code with non standard settings for ANSI_NULLS. Since our routine that saves and reintroduces the customization, uses dynamic TSQL, we run into a problem. We cannot add 'GO' to the script, since 'GO' isn't TSQL and therefore cannot be interpreted in dynamic TSQL. However, I need to issue a 'SET ANSI_NULLS ...' command before creating the trigger to recreate it with the ansi settings it had before deployment. Any idea's how to progress? My latest failure is included below.

    IF EXISTS ( SELECT TOP (1) 1 FROM sys.tables WHERE [name] = 'Test' ) DROP TABLE dbo.Test;
    GO
    CREATE TABLE dbo.Test (i int);
    GO
    
    SET ANSI_NULLS OFF;
    GO
    CREATE TRIGGER dbo.ExecOnUpdateTest ON dbo.Test FOR UPDATE AS RETURN;
    GO
    DROP TRIGGER dbo.ExecOnUpdateTest 
    GO
    
    DECLARE @Definition nvarchar(max);
    
    SET @Definition = N'EXEC(''SET ANSI_NULLS ON'');
    EXEC(''CREATE TRIGGER dbo.ExecOnUpdateTest ON dbo.Test FOR UPDATE AS RETURN;'')';
    
    PRINT @Definition;
    EXECUTE(@Definition);
    
    SELECT T.[name], C.[definition], C.uses_ansi_nulls
    FROM sys.triggers AS T JOIN sys.sql_modules AS C ON C.[object_id] = T.[object_id]
    WHERE T.[name] = 'ExecOnUpdateTest';
    
    

    Wednesday, April 3, 2019 2:20 PM

Answers

  • DECLARE @Definition  nvarchar(max);
    SET @Definition = N' CREATE TRIGGER dbo.ExecOnUpdateTest ON dbo.Test FOR UPDATE AS RETURN;';
    EXEC ('SET ANSI_NULLS ON EXEC(''' + @Definition  + ''')')

    Wednesday, April 3, 2019 4:06 PM

All replies

  • DECLARE @Definition  nvarchar(max);
    SET @Definition = N' CREATE TRIGGER dbo.ExecOnUpdateTest ON dbo.Test FOR UPDATE AS RETURN;';
    EXEC ('SET ANSI_NULLS ON EXEC(''' + @Definition  + ''')')

    Wednesday, April 3, 2019 4:06 PM
  • Thank you, Rachel. This works perfectly for me.
    Thursday, April 4, 2019 7:43 AM