Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    3

    Unanswered: Update before trigger question

    Hi,

    In an update before trigger using FOR EACH ROW, does the :NEW correlation name have visibility to all columns in the affected row or only the columns modified by the update? Specifically, if table X has three columns (col1, col2, col3) and I perform an update that only modifies col1, will :NEW.col2 and :NEW.col3 contain values or do I need to refer to :OLD.col2 and :OLD.col3? I thought :NEW showed all column values but our DBA's have told me that :NEW only shows modified column values.

    Thanks,
    Michael Bailey

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It doesn't matter how you address columns that weren't affected by a statement - both :OLD and :NEW containt the same value.

    Here's an example:
    PHP Code:
    CREATE OR REPLACE TRIGGER brisime
    BEFORE UPDATE ON DEPT
    FOR EACH ROW
    BEGIN
    dbms_output
    .put_line('old deptno ' || :OLD.deptno);
    dbms_output.put_line('new deptno ' || :NEW.deptno);
    --
    dbms_output.put_line('old dname ' || :OLD.dname);
    dbms_output.put_line('new dname ' || :NEW.dname);
    END;
    /
     
    UPDATE DEPT SET dname 'accounting' WHERE deptno 10;
     
    old deptno 10
    new deptno 10
    old dname ACCOUNTING
    new dname accounting 

Posting Permissions

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