Re: Required field for updates

Stephen_CA wrote:
>
> Walt <walt_at_boatnerd.com.invalid> wrote

> > ... how does one make a field required for updates? For instance, we
> > have a table with a field that keeps track of which application last
> > updated the table
> >
> > MYTABLE
> > ------
> > ID PK
> > APPLICATION_ID
> > MORE_STUFF
> >
> > I want to write a PL/SQL trigger that requires the application to
> > specify the appilication_id, and throw an error if the application fails
> > to provide a value. I.e. this SQL command should fail:
> >
> > "UPDATE MYTABLE set MORE_STUFF = 'foo' WHERE ID = 123;"
> >
> > IOW, how does one determine the update list in a PL/SQL trigger? I can
> > check to see if the value changed, but that won't do it.
>
>
> Unless I've completely missed the intent of your post, just include
> this type of logic in a BEFORE UPDATE trigger:
>
> IF :NEW.APPLICATION_ID IS NULL THEN....
Thanks, but that won't do it. If the record already has a non-null value for application_id, :new.application id will contain that value. For instance,

INSERT into MYTABLE VALUES ( 123, 456, 'foo');

UPDATE MYTABLE set MORE_STUFF = 'bar' WHERE ID = 123;

when the trigger fires, :new.application_id is equal to 456, even though the update statement didn't include a value for it.

LKBrwn has the right idea, using the IF UPDATING('application_id') construct.

-- 
//-Walt
//
//