Required field for updates

[Walt]

It's easy to make a field required for inserts, just set it to not null
and don't give it a default.

But 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.

--
//-Walt
//
//

[philippe]

Re: Required field for updates

Why not try to check :new and :old values ?

[Walt]

Re: Required field for updates

philippe wrote:[color=blue]
>
> Why not try to check :new and :old values ?[/color]

Because it doesn't tell me anything. If the application left the field
out of the update list, :old and :new will be the same. If the
application specified a value that's the same as the old value, :old and
:new will be the same.

How does one distinguish the two cases?

--
//-Walt
//
//

[LKBrwn_DBA]

Re: Required field for updates


Try:


IF Updating('APPLI CATION_ID') Then
... Do something ...
Else
raise_applicati on_error(-200001,'No APPLICATION_ID found');
End If;

--
Posted via http://dbforums.com

[Stephen_CA]

Re: Required field for updates

Walt <walt@boatnerd. com.invalid> wrote in message news:<3F1FF76B. 43264144@boatne rd.com.invalid> ...[color=blue]
> It's easy to make a field required for inserts, just set it to not null
> and don't give it a default.
>
> But 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.[/color]

Hi Walt,

Unless I've completely missed the intent of your post, just include
this type of logic in a BEFORE UPDATE trigger:

IF :NEW.APPLICATIO N_ID IS NULL THEN....

Steve

[Walt]

Re: Required field for updates

LKBrwn_DBA wrote:[color=blue]
>
> Try:
>
> IF Updating('APPLI CATION_ID') Then
> .. Do something ...
> Else
> raise_applicati on_error(-200001,'No APPLICATION_ID found');
> End If;[/color]

Thanks. That appears to do it. I've used the IF UPDATING construct
before, but I didn't know you could specify a column. Cool.

You wouldn't happen to have a pointer to some documentation to this
feature, would you? It's not covered in any of my Oracle Press or
O'reilly PL/SQL books.

--
//-Walt
//
//

[Walt]

Re: Required field for updates

Stephen_CA wrote:[color=blue]
>
> Walt <walt@boatnerd. com.invalid> wrote[/color]

[color=blue][color=green]
> > ... 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.[/color]
>
>
> Unless I've completely missed the intent of your post, just include
> this type of logic in a BEFORE UPDATE trigger:
>
> IF :NEW.APPLICATIO N_ID IS NULL THEN....[/color]

Thanks, but that won't do it. If the record already has a non-null
value for application_id, :new.applicatio n 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.applicatio n_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('appli cation_id')
construct.

--
//-Walt
//
//