Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Posts
    54

    Exclamation Unanswered: Oracle Trigger Problem - Row level

    Hi there. I've created a trigger which is supposed to track all changes made to a table. (adding records, deleting, or modifying)
    It's basically an audit.
    My problem is that in certain scenarios, the audit is not working - adding a record doesn't trigger this event, deleting a record doesn't trigger either. Only when I modify a NON NULL field on an EXISTING record, does this trigger work.

    I did a small test and I think my general problem is related to NULLS.
    For example, let's say I have record 123 and I modify fieldA's current value from "abc" to "def", the system records this properly. But, if fieldA is currently NULL, and I change it to "def", nothing is recorded.
    For a new record, since all the "old" values would be nothing, I'm guessing this is why the system in not recording anything.
    here's what the trigger looks like in part:

    Code:
    CREATE OR REPLACE
    TRIGGER AUDITCHANGES
    BEFORE INSERT OR DELETE OR UPDATE 
      ON WTB
      FOR EACH ROW
      DECLARE
      
    BEGIN
      IF :new."InterviewDate" != :old."InterviewDate" THEN
        INSERT INTO WTAudit (MYKEY, MYKEYNAME,DTCHG,FLDNAME, userid, oldval, newval) 
        VALUES (:old."StNo", 'St ID', SYSDATE, 'InterviewDate', SYS_CONTEXT('USERENV','OS_USER'), :old."InterviewDate",:new."InterviewDate");
      END IF;
      IF :new.BNP7 != :old.BNP7 THEN
        INSERT INTO WTAudit (MYKEY, MYKEYNAME, DTCHG,FLDNAME, userid, oldval, newval) 
        VALUES (:old."StNo", 'StID', SYSDATE, 'BNP7', SYS_CONTEXT('USERENV','OS_USER'), :old.BNP7,:new.BNP7);
      END IF;
    Any suggestions would be appreciated!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >For a new record, since all the "old" values would be nothing, I'm guessing this is why the system in not recording anything.
    Yes.

    You can not use "=" or "!=" with NULL.

    OR ld."InterviewDate" IS NULL
    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
    Jul 2007
    Posts
    54

    thank you

    Quote Originally Posted by anacedent
    >For a new record, since all the "old" values would be nothing, I'm guessing this is why the system in not recording anything.
    Yes.

    You can not use "=" or "!=" with NULL.

    OR ld."InterviewDate" IS NULL
    I modified the If statement to look like:

    Code:
      IF :new."InterviewDate" != :old."InterviewDate" OR (:old."InterviewDate" IS NULL) THEN
        INSERT INTO WITAudit (MYKEY, MYKEYNAME,DTCHG,FLDNAME, userid, oldval, newval) 
        VALUES (:old."StudyNo", 'Study ID', SYSDATE, 'InterviewDate', SYS_CONTEXT('USERENV','OS_USER'), :old."InterviewDate",:new."InterviewDate");
      END IF;
    and that fixed the issue for the new records...
    but do you have any hunches or suggestions for tracking / auditing records that are deleted?

    Thanks.

  4. #4
    Join Date
    Jul 2007
    Posts
    54

    follow up...

    I guess i can just do the opposite ... ??

    Code:
     IF (:new."InterviewDate" != :old."InterviewDate") OR (:old."InterviewDate" IS NULL)  OR (:new."InterviewDate"  IS NULL)

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but do you have any hunches or suggestions for tracking / auditing records that are deleted?

    what does :new contain for DELETE?
    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.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    update - use ld, :new

    delete - use ld

    insert - use :new.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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