| Validation problem when updating [message #471608] |
Mon, 16 August 2010 05:19  |
Jimbo123
Messages: 2
Registered: August 2010
|
Junior Member |
|
|
hi people, just wondering if you would be so kind as to help me out. I have a car field in which only one type of car may be entered. i.e. two Ford's would not be allowed.
To ensure this works i check what the user is inputting with what is already in the database by the following code:
DECLARE
alert number;
CURSOR c_validate IS
SELECT car
FROM transport
WHERE car = :car;
v_car transport.car%type;
BEGIN
open c_validate;
fetch c_validate INTO v_car;
close c_validate;
if (v_car is not NULL) THEN
alert:= show_alert('DUPLICATE_CAR_ALERT');
RAISE FORM_TRIGGER_FAILURE;
end if;
END;
My problem arises if the user updates the car to a different type, but then changes their mind and converts it back to the model of car it was before.
ie, record contains car FORD, user decides they want to change it to Renault but before saving changes their mind again and decides its is best left as FORD, now when the user re-enters FORD the validation alert comes up as FORD is already in the database.
Does anyone have any idea what I could do about this or if there is any way round this? is there a way to check the original value before it was changed so:
if :car = original value then thats fine?
Thanks for reading.
[Updated on: Mon, 16 August 2010 05:22] Report message to a moderator |
|
|
|
| Re: Validation problem when updating [message #471611 is a reply to message #471608] |
Mon, 16 August 2010 05:29  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Use rowid. It uniquely identifies a row.
DECLARE
n number;
alert number;
BEGIN
SELECT 1 INTO n
FROM transport
WHERE car = :transport.car
AND rowid != nvl(:transport.rowid, 0)
AND rownum = 1;
alert:= show_alert('DUPLICATE_CAR_ALERT');
RAISE FORM_TRIGGER_FAILURE;
EXCEPTION WHEN no_data_found THEN
NULL;
END;
I nvl'd the rowid as it will be null if you are creating a new record.
Couple of notes - implicit cursors (what I used above) are more efficient than explicit cursors (which you used).
You should always prefix item names with the corresponding block name.
Also can you have a read of the forum guide and follow it next time you post. ta.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
