Re: Update trigger, detecting missing columns

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 20 Dec 2005 16:03:16 -0800
Message-ID: <1135123383.641521@jetspin.drizzle.com>

Chris L. wrote:
> DA Morgan wrote:
>

>>Chris L. wrote:
>>
>>>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
>>
>>This request makes little sense. What is it you are actually trying
>>to do? And why?
>>
>>What is it that :OLD and :NEW can't do that you actually need?

>
>
> Daniel: I want to calculate a bonus, (using complex formulas and other
> tables) except when the value comes specified on the UPDATE.
>
> update ZZ set C=1 where A=2;
> /* bonus wasn't specified, trigger must calculate it */
>
> update ZZ set C=1, Bonus=10 where A=2;
> /* bonus was specified, use that value, forget complex formulas */
>
> I can not tell, using :new and :old, if Bonus was specified or not.
>
> As Quesnel said, comparing :new versus :old would not handle the case
> of an update that "sets the value of Bonus to what it was previously".
>
> You CAN tell when columns are "missing" on insert statements asking if
> :new is null, but that's not the case with an insert, or so it seems so
> far.
>
> insert into ZZ (A, C) values (3,4);
>
> /* you can detect on the insert trigger that :new.B is null and act
> accordingly */
>
> Greetings
> Cris

Then what's wrong with:

IF :NEW.bonus IS NULL THEN

or

IF :NEW.bonus IS NULL and :OLD.bonus IS NOT NULL THEN

I still don't see what it is you are trying to do that you don't already have the tools to handle.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Tue Dec 20 2005 - 18:03:16 CST