Home » Developer & Programmer » Forms » Avoid Duplication in three tables (oracle forms builder6!)
Avoid Duplication in three tables [message #576055] Thu, 31 January 2013 03:36 Go to next message
nada_am
Messages: 9
Registered: January 2013
Location: KSA
Junior Member
Dear all,

I'm creating oracle form to allow the user to register a new record in a table, I want to check the duplication in Four tables!
which means, the user is able to save the record if it's NOT already registered in the other four tables.By checking P_ID (user parameter).

this is my code:

Declare
v_count number;
begin
select count(*) into v_count ---Checking the 1st table
from 1_table
where p1_id=:p_id;
if (v_count>0) then
message ('Duplicate');
RAISE form_trigger_failure;
ELSE
select count(*) into v_count -------checking the 2nd table
from 2_table
where p2_id=:p_id;
if (v_count>0) then
message ('Duplicate');
RAISE form_trigger_failure;
ELSE
select count(*) into v_count ----checking the 3rd table
from 3_table
where 31_id=:p_id;
if (v_count>0) then
message ('Duplicate');
RAISE form_trigger_failure;
else
select count(*) into v_count --------checking the 4th table
from 4_table
where p4_id=:p_id;
if (v_count>0) then
message ('Duplicate');
RAISE form_trigger_failure;
end if;
end if;
end if;
end if;
END;

IS it correct ? please help me I'm confused ,,,thank you in advance
Re: Avoid Duplication in three tables [message #576064 is a reply to message #576055] Thu, 31 January 2013 04:13 Go to previous messageGo to next message
nada_am
Messages: 9
Registered: January 2013
Location: KSA
Junior Member
I'm using the above code under WHEN-VALIDATE-ITEM for p_id trigger .... plzzzz help!
Re: Avoid Duplication in three tables [message #576066 is a reply to message #576064] Thu, 31 January 2013 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

I don't know forms so take into account only the pure PL/SQL part:
begin
  select 1 into v_count 
  from ( select 1 from 1_table where p1_id=:p_id and rownum=1 
         union all 
         select 1 from 2_table where p1_id=:p_id and rownum=1 
         union all 
         select 1 from 3_table where p1_id=:p_id and rownum=1 
         union all 
         select 1 from 4_table where p1_id=:p_id and rownum=1 )
  where rownum = 1;
  message ('Duplicate');
  RAISE form_trigger_failure;
exception
  when no_data_found then null;
end;


Regards
Michel

Re: Avoid Duplication in three tables [message #576077 is a reply to message #576066] Thu, 31 January 2013 06:38 Go to previous messageGo to next message
cookiemonster
Messages: 10858
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your original code should do the job by the looks of it but I'd use Michel's version, it's simpler and more efficient.
Re: Avoid Duplication in three tables [message #576250 is a reply to message #576077] Fri, 01 February 2013 23:28 Go to previous message
nada_am
Messages: 9
Registered: January 2013
Location: KSA
Junior Member
Thank you michel soooooo much. your code did help me ,, warmest Regards
Previous Topic: Forms 11g Applet blank Screen
Next Topic: run report in forms 11
Goto Forum:
  


Current Time: Thu Jul 31 05:18:43 CDT 2014

Total time taken to generate the page: 0.16008 seconds