Hi Alan,

Thanks. I'm an ID10T! I modified the before insert trigger and
forgot to remove the gen_id.

I thought I had even looked that over before posting here. I'm such a
schmuck.

Thanks again.

Ryan.

--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:
>
> > 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
> >
> >
> >
> >
>