> FB1.54/Windows
>
> I have two tables in a master/detail type of relation.
>
> In a stored proc I create and insert a record for the master table and
> then in the same stored proc try to create and insert a detail record
> pointing back to the master record created in the first insert.
>
> I thought that because it was in the same stored proc that the
> transaction handling would allow me to do this type of operation, but
> I keep getting a foreign key violation on the detail table for the new
> master table record.
>
> What am I doing wrong?

show us the BEFORE INSERT trigger you have on the maser table... you're
probably creating a newID for the table when you really weant ot insert the
ID you're getting on the first line.
Alan

>
> Here is the body of the SP.
>
> ************ Start block **********
>
> create procedure SUBMITLWO (
> INDUSTRYID integer,
> GENERALID integer,
> ORGANIZATIONID integer,
> MEMBERID integer,
> LWOSHORTDESC varchar(253),
> LWODETAILS blob,
> LWOCLOSE date,
> LWOACTIVE smallint,
> LWOAWARDED smallint,
> LWOAWARDDATE date,
> LWORXPLCNTREQ smallint,
> VALIDORGS varchar(250))
> as
> declare variable dv integer;
> declare variable wIdx integer;
> declare variable wCnt integer;
> declare variable wStr varchar(250);
> begin
> dv = gen_id(industrylwo_gen, 1);
> insert into industrylwo
> (LWOID, INDUSTRYID, GENERALID, ORGANIZATIONID,
> MEMBERID, LWOREFID, LWOSHORTDESC, LWODETAILS,
> LWOSUBMITTED, LWOCLOSE, LWOACTIVE, LWOAWARDED,
> LWOAWARDDATE, LWORXPLCNTREQ)
> values
> (:dv, :industryid, null, :organizationid,
> :memberid, 'RWO-'||cast(:dv as varchar(15)), :lwoshortdesc,
> :lwodetails,
> 'TODAY', :lwoclose, :lwoactive, :lwoawarded,
> :lwoawarddate, :lworxplcntreq);
>
> wCnt = udf_CountSections(:ValidOrgs, ',');
> wIdx = 0;
> while (wIdx < wCnt) do
> begin
> wIdx = wIdx + 1;
> wStr = udf_ParseSection(:ValidOrgs, :wIdx, ',');
> insert into industrylwoorganizations (lwoID, orgid) values (:dv,
> cast(:wStr as integer));
> end
> end
>
> ******** end block *******
>
> CountSections and ParseSections are custom written UDF functions. I
> have tested them separately and I know they work. The actual error
> message that I get is:
>
> ISC ERROR CODE:335544466 ISC ERROR MESSAGE: violation of FOREIGN KEY
> constraint "FK1_INDUSTRYLWOORGANIZATIONS" on table
> "INDUSTRYLWOORGANIZATIONS"
>
> FK1_INDUSTRYLWOORGANIZATIONS is the foreign key reference back to
> industrylwo.
>
> Please someone tell me what I'm missing and/or doing wrong here????
>
> Ryan
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>