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
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.
[Edited by mber on 03-21-2001 at 04:25 PM]
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.
You are the right person, please check if it is correct:
Application Developerís Guide - Fundamentals
Release 2 (8.1.6)
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 . . .
. . . IF UPDATING (íSALí) THEN . . . END IF;
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;
I tested it by this way, but it doesn't work.
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).
[Edited by jmodic on 03-23-2001 at 05:04 PM]