Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: Update trigger to triggered when old value is NULL

    Hi,

    I have an update trigger that checks if a column has changed. I do this with referencing to OLD as DELETED and NEW as INSERTED, for each row:

    IF(DELETED.COL1 <> INSERTED.COL1) THEN...

    This works fine if COL1 has both a new and old value. However, if the OLD value for COL1 is 'NULL', then the IF -statement is not true and the logic inside the IF-statement is not executed. I find this strange, as IF ( NULL <> 'myString' ) should in my opinion return true.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    According to the manual, if either of compared values is unknown (NULL), the comparison result is neither true nor false. Use the IS NULL expression to compare unknown (NULL) values.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by n_i View Post
    According to the manual, if either of compared values is unknown (NULL), the comparison result is neither true nor false. Use the IS NULL expression to compare unknown (NULL) values.
    Hmm, there is no generic test for this? Is there no easier way to test this than the statement below?:

    IF((DELETED.COL1 <> INSERTED.COL1) OR (DELETED.COL1 IS NULL AND INSERTED.COL1 IS NOT NULL) OR (DELETED.COL1 IS NOT NULL OR INSERTED.COL1 IS NULL ) THEN...

    The 1. statement checks if the values differ, the 2. and 3. statements check if a value has changed from/to NULL.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by SoftwareEngineer3 View Post
    Hmm, there is no generic test for this? Is there no easier way to test this than the statement below?:

    IF((DELETED.COL1 <> INSERTED.COL1) OR (DELETED.COL1 IS NULL AND INSERTED.COL1 IS NOT NULL) OR (DELETED.COL1 IS NOT NULL OR INSERTED.COL1 IS NULL ) THEN...

    The 1. statement checks if the values differ, the 2. and 3. statements check if a value has changed from/to NULL.
    You may use VALUE function and use as its 2nd argument a value that doesn't belong to that column:
    IF (VALUE(DELETED.COL1, -1) <> VALUE(INSERTED.COL1, -2))
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by SoftwareEngineer3 View Post
    if the OLD value for COL1 is 'NULL', then the IF -statement is not true and the logic inside the IF-statement is not executed. I find this strange, as IF ( NULL <> 'myString' ) should in my opinion return true.
    What you refer to is not the "<>" operator, but the "IS DISTINCT FROM" operator:

    Code:
    IF(DELETED.COL1 IS DISTINCT FROM INSERTED.COL1) THEN...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    At DB2 v10.1, the " IS DISTINCT FROM" syntax is not available in DB2 for LUW (yet) , it is available on DB2 for Z/OS platform.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •