Re: Update trigger, detecting missing columns

Chris L. schrieb:
> Hello all,
>
> [Oracle 9i]
>
> Need to detect, in a "before update for each row" trigger, if a
> specific column had a value informed on the SQL statement.
>
> Example: Table ZZ with A, B, C columns
>
> update table ZZ set C=1 where A=2; (column B wasn't informed)
>
> update table ZZ set C=1,B=3 where A=2; (column B was informed)
>
> Assume both sentences hit one record. (A=Primary key)
>
> looking at :new.B doesn't help because that holds the value for B on
> that record.
> (If the record is A=2, B=1000, C=3, then :new.B evaluates to 1000 on
> the first update and to 3 on the second update).
>
> Any way to do it?
> Thanks in advance
> Cris
>

You can define a trigger which will fire only if column of interest is updated. Something like this:

CREATE OR REPLACE TRIGGER trg_z
BEFORE UPDATE OF B ON ZZ
FOR EACH ROW
BEGIN

• your action DBMS_OUTPUT.PUT_LINE('B was updated'); END; /

Best regards

Maxim Received on Tue Dec 20 2005 - 11:30:03 CST