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]

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

[Walt]

philippe wrote:

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

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]

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]

Walt <wa**@boatnerd. com.invalid> wrote in message news:<3F******* ********@boatne rd.com.invalid> ...

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.

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]

LKBrwn_DBA wrote:

Try:

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

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]

Stephen_CA wrote:

Walt <wa**@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.APPLICATIO N_ID IS NULL THEN....

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
//
//