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
//
// 6 4230
Why not try to check :new and :old values ?
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
//
//
Try:
IF Updating('APPLICATION_ID') Then
... Do something ...
Else
raise_application_error(-200001,'No APPLICATION_ID found');
End If;
--
Posted via http://dbforums.com
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
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
//
//
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
//
// This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |