Results 1 to 6 of 6

Thread: Triggers

  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: Triggers

    Just a quick question.

    Normally in sql statements, comparing anything with NULL is problematic. In a trigger, does :new.id != ld.id create the same kind of problem on an insert when the ld.id would be presumably a null value because it's a new record?

    Thanks,

    Robert

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >comparing anything with NULL is problematic.
    Not true!
    When A_VALUE IS NULL


    WHEN B_VALUE IS NOT NULL
    are both valid can can be useful.

    in an AFTER INSERT trigger, the value would be NOT NULL.

    It appear the most likely problem is PEBKAC.
    I suggest you spend some time RTFM
    http://download-west.oracle.com/docs...a96524/toc.htm
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by rtnetworks
    Normally in sql statements, comparing anything with NULL is problematic. In a trigger, does :new.id != :old.id create the same kind of problem on an insert when the :old.id would be presumably a null value because it's a new record?
    As you suspected, it makes no sense using :old in a trigger that fires on insert. So don't.

  4. #4
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb

    If I have understood question properly, problem actually lying in handling null values in :new.id or ld.id.

    Try implementing following code:

    CREATE OR REPLACE TRIGGER TEMP_TRG
    AFTER UPDATE/INSERT/DELETE ON Tab1
    REFERENCING NEW AS newrow OLD AS oldrow
    FOR EACH ROW
    BEGIN
    IF (ldrow.id <> :newrow.id) THEN
    -- pl/sql block

    ElSIF (ldrow.id IS NULL AND :newrow.id IS NOT NULL) THEN
    -- pl/sql block

    ElSIF (:newrow.id IS NULL AND ldrow.id IS NOT NULL) THEN
    -- pl/sql block
    END IF;
    END;

    In above way you can handle null as well as three different / same pl/sql block can be used. With little manipulation code you can use above trigger for any of INSERT/ UPDATE/DELETE trigger.

    Any comments are welcome.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Or more simple:
    Code:
    CREATE OR REPLACE TRIGGER TEMP_TRG
    AFTER UPDATE/INSERT/DELETE ON Tab1
    FOR EACH ROW
    BEGIN
      IF NVL(:old.id,'?') <> NVL(:new.id,'?') THEN
      -- pl/sql block
      END IF;
    END;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    May 2004
    Posts
    184
    Thank you. That is exactly what I needed to know. I was just trying to be sure that I had to account for null values in a trigger in the same way I did outside of a trigger.

    Thank you to all of you who had positive suggestions.

    Robert

Posting Permissions

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