473,324 Members | 2,370 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Required field for updates


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
6 4230
Why not try to check :new and :old values ?
Jul 19 '05 #2
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

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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Lorenzo Bolognini | last post by:
Hi all, i need to detect whether a field is required or not. I'm using this code for building a string to convert later to an array (by Split) of which each element matches the field index (ex....
16
by: Georges Heinesch | last post by:
Hi. My form contains a control (cboFooBar), which has an underlying field with the "Required" property set to "Yes". Now, while filling out all the controls of the form, I have to fill out this...
2
by: bufbec1 | last post by:
I am pretty good with Access, but do not understand VBA. I have researched this topic and see only VBA answers, so I hope someone can help with my specific question. I have 2 fields for an...
3
by: Orchid | last post by:
Hello All, Hope someone can help me on my required field problems. I have a form base on a table for users to input new Employees. There are 4 fields that cannot be Null when entering new...
2
by: Miro | last post by:
I will ask the question first then fumble thru trying to explain myself so i dont waste too much of your time. Question / Statement - Every mdb table needs a PrimaryKey ( or maybe an index - i...
5
by: rdemyan via AccessMonster.com | last post by:
I have a need to add another field to all of my tables (over 150). Not data, but an actual field. Can I code this somehow. So the code presumabley would loop through all the tables, open each...
6
by: Walt | last post by:
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...
5
by: Cubicle Intern | last post by:
Hi, I have a form with multiple fields that confirmed before the form is submitted (ex. email field needs to be completed before the form can be submitted). Once the required fields are...
4
by: PW | last post by:
Hi, I set up a relationship between two tables with the itineraryid fields in both tables: tblDailyItinerary tblDailyMeals I have a form that writes a record to tblDailyItinerary that...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.