Which column is updated
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Which column is updated

  1. #1
    Join Date
    Oct 2000
    Posts
    123
    Hi,

    I have a trigger somthing like:

    create .....after update of col1,col2... on table t
    ---- I want to identify which column is updated:
    IF :new.col1 <> :0ld.col1 OR (:new.col1 is null and :0ld.col1 is not null) OR (:new.col1 is not null and :0ld.col1 is null) THEN
    do...1;
    ELSE
    .....
    END IF;

    My question:

    Can I use "IF updating('col1') instead of the long "<> or NUll" things above? I saw them in the docus, but it seems not working for me? Please help.

    Thanks

    [Edited by mber on 03-21-2001 at 04:25 PM]

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, you can't do that. You can use "IF UPDATING THEN ...." if you have a combined trigger (a single trigger for both insert and update for example) and want to perform different actions based on the type of your DML, but you can not use this to identify the columns that are actualy changed.

    But you could write your code much more "readable" by using NVL, something like:

    IF NVL(:new.col1,-1) <> NVL(:0ld.col1,-1) THEN do..1
    ELSIF NVL(:new.col2,'#') <> NVL(:0ld.col1,'#') THEN do..2
    ...

    I'm assuming col1 is of type NUMBER with only positive integers allowed and col2 of type VARCHAR2 that can never be set a value of '#'. So basicaly you should replace any null value with some imposible value of appropriate type.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Oct 2000
    Posts
    123
    Hi, Jeff,

    You are the right person, please check if it is correct:

    Quote:
    ===============
    Oracle8 i
    Application Developerís Guide - Fundamentals
    Release 2 (8.1.6)
    December 1999
    Part No. A76939-01
    ---------------------- Page 500

    In an UPDATE trigger, a column name can be specified with an UPDATING conditional predicate to determine if the named column is being updated. For example, assume a trigger is defined as the following:
    CREATE OR REPLACE TRIGGER . . .
    . . . UPDATE OF Sal, Comm ON Emp_tab . . .
    BEGIN
    . . . IF UPDATING (íSALí) THEN . . . END IF;
    END;

    The code in the THEN clause runs only if the triggering UPDATE statement updates
    the SAL column. The following statement fires the above trigger and causes the
    UPDATING (sal) conditional predicate to evaluate to TRUE:
    UPDATE Emp_tab SET Sal = Sal + 100;
    ===============================
    Quote end.

    I tested it by this way, but it doesn't work.

    Please help

    Thanks

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, I'm not Jeff, but since I've given you a wrong answer to your original question I'm replying to this one also...

    I wasn't aware of this option in triggers. After reading your quote I've checked it out and it does work as described! I've checked it on 8.1.5 (the only one that I have handy at this moment), it works. What surprised me in the sintax is that you actualy have to put column names in single quotes. And even though you are passing column names as litterals, they are case insensitive... I don't know why it doesn't work for you, check your trigger syntax once more.

    But there is one fundamental difference between this kind of determining if column is being updated and the one you used in your original question (the one with IF :new.col1 <> :0ld.col1 OR .... ENDIF). The one with "IF .. <> .. END IF" will fire only if column value is realy changed by the update, while the one with "IF UPDATING (col1).." will fire even if your update does not realy change the existing value in col1 (i.e. when you overwrite the value in col1 with the same value it allready have).

    HTH,

    [Edited by jmodic on 03-23-2001 at 05:04 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width