OraFAQ Forum: Forms » Problem in inserting values using preInsert trigger

Home » Developer & Programmer » Forms » Problem in inserting values using preInsert trigger (Forms 10g / Windows XP)

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

Problem in inserting values using preInsert trigger [message #477454]

Thu, 30 September 2010 14:20

nikhilk411
Messages: 33
Registered: September 2010
Location: Mumbai

Member

Hi,

I am facing a problem while inserting primary keys using a sequence. Following is my case

I have a Master Table, say M_A, and 2 detail tables D_1 and D_2.
I am trying to generate primary keys for the master and detail table as well as the reference keys for the detail tables using sequence.

I created a pre-insert trigger, say preInsertTRIG

proc_ABC(pri_key_master OUT VARCHAR2,
pri_key_detail1 OUT VARCHAR2,
fk_detail1 OUT VARCHAR2,
pri_key_detail2 OUT VARCHAR2,
fk_detail2 OUT VARCHAR2)

);
as
v_no NUMBER(10);
BEGIN
select id_seq.next val into v_no from dual;

pri_key_master := 'PK'||to_char(v_no); --assign P.K to mast
pri_key_detail1 := 'PK_D1'||to_char(v_no);
pri_key_detail2 := 'PK_D2'||to_char(v_no);
fk_detail1 := pri_key_master;
fk_detail2 := pri_key_master;

end;
end;

I am able to insert the P.K of the master table as well as the P.K of one of the detail table. However, it fails to insert the P.K of 2nd detail table and reference keys for both the detail tables.

Can someone kindly guide where am I going wrong?

P.S: I know there are other simple methods available in Forms, but I have to do it by this procedure only

Report message to a moderator

Re: Problem in inserting values using preInsert trigger [message #477463 is a reply to message #477454]

Thu, 30 September 2010 15:35

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What do you do with that procedure? Where do you call it and how? What do you do with OUT parameters' values?

P.S. Would you mind posting accurate code? The one you posted is wrong (it would NEVER compile).

[Updated on: Thu, 30 September 2010 15:38]

Report message to a moderator

Re: Problem in inserting values using preInsert trigger [message #477469 is a reply to message #477463]

Thu, 30 September 2010 17:22

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Are you only allowed to have one detail record in each detail table for each master record?

I really don't understand why you're using the same number for all three tables.

Also "fails to insert" doesn't really tell us much - do you get an error? which one?

Report message to a moderator

Re: Problem in inserting values using preInsert trigger [message #477849 is a reply to message #477469]

Mon, 04 October 2010 17:03

nikhilk411
Messages: 33
Registered: September 2010
Location: Mumbai

Member

@Littlefoot and cookiemonster - ya i figured out the above code is wrong. I should have wrote 3 preInsert statements for the 3 tables. (@littlefoot - thanks for the hint). I was using my company's oracle to java migration tool, in which I had to feed the primary keys generated through the sequences through the "OUT" variables.
My mistake was :- 1. I should have written 3 preInsert statements (I was inserting values simultaneously into 3 tables - 1 master + 2 detail)
2. Shouldn't have tried to explicitly assign value to the F.K the way I was trying to do.
3. Some wrong settings set by me within our conversion tool.

So my prob has got resolved. Thanks a lot :-) Smile