By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,190 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

Required field for updates

P: n/a

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
//
//
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Why not try to check :new and :old values ?
Jul 19 '05 #2

P: n/a
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
//
//
Jul 19 '05 #3

P: n/a

Try:
IF Updating('APPLICATION_ID') Then
... Do something ...
Else
raise_application_error(-200001,'No APPLICATION_ID found');
End If;

--
Posted via http://dbforums.com
Jul 19 '05 #4

P: n/a
Walt <wa**@boatnerd.com.invalid> wrote in message news:<3F***************@boatnerd.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.APPLICATION_ID IS NULL THEN....

Steve
Jul 19 '05 #5

P: n/a
LKBrwn_DBA wrote:

Try:

IF Updating('APPLICATION_ID') Then
.. Do something ...
Else
raise_application_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
//
//
Jul 19 '05 #6

P: n/a
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.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
//
//
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.