Subject Help! Problems with SPs and Transactions I think?
Author sllimr7139
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?

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