OraFAQ Forum: Forms » Avoid Duplication in three tables

Home » Developer & Programmer » Forms » Avoid Duplication in three tables (oracle forms builder6!)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Avoid Duplication in three tables [message #576055]

Thu, 31 January 2013 03:36

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

Report message to a moderator

Re: Avoid Duplication in three tables [message #576064 is a reply to message #576055]

Thu, 31 January 2013 04:13

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!

Report message to a moderator

Re: Avoid Duplication in three tables [message #576066 is a reply to message #576064]

Thu, 31 January 2013 04:18

Michel Cadot
Messages: 68645
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

Report message to a moderator