locked
SET ANSI_NULLS ignored inside triggers RRS feed

  • Question

  • Any SET statement can be specified inside a trigger. The SET option selected remains in effect during the execution of the trigger and then reverts to its former setting.

    from: http://msdn.microsoft.com/en-us/library/ms189799.aspx

     

    BUT the following doesn't work!! (it should print 'works'. Try just running the set ansi nulls off + if statement for expected results)

    create table test_ansi_nulls (colA int)
    go
    set ansi_nulls on
    go
    create trigger tr_test on test_ansi_nulls
    after insert
    as
    begin
    set nocount on
    set xact_abort on
    set ansi_nulls off
    if (null=null)
        print 'works'
    end
    go
    insert into test_ansi_nulls values(1)
    go
    drop table test_ansi_nulls
    go

    The profiler shows the trigger firing the nocount and xact abort set options but the ansi nulls one NEVER appears.

    my SQL engine version is : Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

    Can someone with a newer version of sql server try it on their machine. I haven't been able to find any info in any of the hotfixes doco that shows that this was fixed

     

    Wednesday, September 21, 2011 6:06 AM

Answers

  • There are many idiocyncrancies with the SET commands. The setting for ANSI_NULLS is saved with the object. That is, the setting when the object is created applies, not the run-time seting. I seem to recall that if you change the setting, that only affect dynamic SQL that you run.

    Now, while this is utterly confusing, there is a simple workaround: don't touch it! ANSI_NULLS is a legacy setting, and there is zero reason to set ANSI_NULLS OFF in new code. If you feel that you have reason to do this, please state your real problem, and we should be able to suggest a better solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by jakubk Wednesday, September 21, 2011 7:40 AM
    Wednesday, September 21, 2011 7:22 AM

All replies

  • Here's some more data:

     

    create table test_ansi_nulls (colA int)
    go
    set ansi_nulls off
    go
    create trigger tr_test on test_ansi_nulls
    after update
    as
    begin
    set nocount on
    set xact_abort on
    set ansi_nulls off
    declare @iColA int, @dColA int

    select 'no where clause',* from inserted i,deleted d-- where i.colA <> d.ColA
    select * from inserted i,deleted d where i.colA = d.ColA
     
    -- compare pre and post update value of ColA column.
    if exists(select 1 from inserted i,deleted d where i.colA = d.ColA)
        print 'works 1'
    if (null=null)
        print 'works 2'

    select @iColA = i.ColA,@dColA = d.ColA from inserted i,deleted d

    if (@iColA = @dColA)
        print 'works 3'   
       
    end
    go
    insert into test_ansi_nulls(colA) values(null)
    go

    SET ANSI_NULLS ON

    update test_ansi_nulls set colA = null

    drop table test_ansi_nulls
    go

    ----------


    (1 row(s) affected)
    works 2
    works 3

    (1 row(s) affected)

    ----------

     

    It appears that creating the trigger with set ansi_nulls off causes it to work sometimes. But only for literals and @variables. Fields that are null are still treated as if ansi_nulls is on....

    Wednesday, September 21, 2011 7:05 AM
  • There are many idiocyncrancies with the SET commands. The setting for ANSI_NULLS is saved with the object. That is, the setting when the object is created applies, not the run-time seting. I seem to recall that if you change the setting, that only affect dynamic SQL that you run.

    Now, while this is utterly confusing, there is a simple workaround: don't touch it! ANSI_NULLS is a legacy setting, and there is zero reason to set ANSI_NULLS OFF in new code. If you feel that you have reason to do this, please state your real problem, and we should be able to suggest a better solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by jakubk Wednesday, September 21, 2011 7:40 AM
    Wednesday, September 21, 2011 7:22 AM
  • Ok, that explains why setting it off for the session that runs the create trigger code kind of works.

    I'm auditing updates by etl processes of business fields that allow nulls. Before/after fields in the auditing table are declared as sql_variant

    I've got the workaround going ...

        WHERE I.field<> D.field
        OR (I.field IS NULL AND D.field IS NOT NULL)
        OR (I.field IS NOT NULL AND D.field IS NULL)

    But going by booksonline I thought setting it to off and just having the i.field<>d.field predicate should be enough! Alas, it was not

    Wednesday, September 21, 2011 7:40 AM
  • Yes, with ANSI_NULLS OFF you can reduce your code with a few characters, but it is not a good solution. Sooner or later you will be bitten - there are features in SQL Server when ANSI_NULLS must be ON.

    There is a Connect requestion for adding an operator from ANSI SQL to handle this NULL-comparison problem:
    http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286422
    Votes are welcome!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, September 21, 2011 6:52 PM
    Wednesday, September 21, 2011 10:46 AM