Re: Update trigger, detecting missing columns

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 Received on Tue Dec 20 2005 - 16:21:11 CST