Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Update trigger, detecting missing columns
"Chris L." <diversos_at_uol.com.ar> wrote in message
news:1135096271.423386.22640_at_g44g2000cwa.googlegroups.com...
> 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
>
I don't know what you mean by informed? DO you mean different? If so then
compare the ole and new values of B and if they are the same then B hasn't
changed. Remember that if B can be null that NULL!=NULL so you have to test
for NULL=NULL condition.
Jim
Received on Tue Dec 20 2005 - 11:34:18 CST