Hi everyone,
We have a data entry screen designed using Oracle Forms 10g. The database is Oracle database 11g.
The screen is used to record the transactions that are performed on a juvenile when he/she is admitted to any of the juvenile centers administered by our ministry.
When a juvenile is first admitted to the center, his id number, the center id, and the date are recorded. These three fields constitute the primary key of the master table of the form.
There is a detail table which records the juvenile's transactions when he is in the center.
If the juvenile is checked out of the center, the system updates a flag in the master table and set its value to 1 to indicate that the record is closed.
To prevent the users from entering a record for a juvenile when there is already an open record for him (he is already admitted to one of the centers), we use the following code:
declare
dummy number;
exc1 exception;
begin
select count(*) into dummy from juvenile_master where juvenile_id=:juvenile_id and closed=0;
if dummy<>0 then
alert_the_user
raise exc1;
end if;
when exc1 then raise form_trigger_failure;
end;
This code is found the in the when-validate-item trigger of the juvenile_id field on the form.
My question:
1- There is a recent case where the users where able to enter two records with closed=0. Is the code we are using in any way faulty?
2- Is the method we are using the best approach?
3- would the datatype of the field closed in the form and table affect the validation (e.g. if the datatype in the table is varchar2(1) and we are not using single quotes in the above code?)
Thank you in advance.