gregatacd@... wrote:

> Environment: Firebird 2.0
>
> I have 2 tables in a master/child relationship as follows:
>
> CREATE TABLE MASTER (
> MASTERID BIGINT NOT NULL,
> DESC VARCHAR(200)
> );
>
> CREATE TABLE CHILD (
> CHILDID BIGINT NOT NULL,
> MASTERID BIGINT NOT NULL,
> DESC VARCHAR(200)
> );
>
> MASTERID is the PK in MASTER, and CHILDID is the PK in CHILD, and
> CHILD.MASTERID is properly set up as a foreign key to MASTER.
>
> Both PKs are auto-populated with generators via an insert trigger.
>
> Now, when I create my database, I want to automatically populate these
> tables with data right from the start by executing as SQL script.
>
> For example:
>
> INSERT INTO MASTER (DESC) VALUES ("FIRST");
> INSERT INTO MASTER (DESC) VALUES ("SECOND");
> INSERT INTO MASTER (DESC) VALUES ("THIRD");
>
> This works fine for the master table, since the trigger takes care of
> the PK assignment. The problem comes when I want to insert the CHILD
> entries. I don't know what the ID is for the MASTER entry...
>
> INSERT INTO MASTER (DESC) VALUES ("FIRST");
> INSERT INTO CHILD (MASTERID, DESC)
> VALUES (<dont know this!>, 'FIRST CHILD OF FIRST');
> INSERT INTO CHILD (MASTERID, DESC)
> VALUES (<dont know this!>, 'SECOND CHILD OF FIRST');
>
> I know that in FB 2.0, there is an extended syntax
>
> INSERT INTO MASTER (DESC) VALUES ("FIRST") RETURNING MASTERID INTO
> :varMASTERID;
> INSERT INTO CHILD (MASTERID, DESC)
> VALUES (:varMASTERID, 'FIRST CHILD OF FIRST');
> INSERT INTO CHILD (MASTERID, DESC)
> VALUES (:varMASTERID, 'SECOND CHILD OF FIRST');
>
> But this requires a variable definition, which is not available in a SQL
> script such as this.
>
> I suppose I could use the new EXECUTE BLOCK statement for this, but is
> there a limitation as to the size of an EXECUTE BLOCK statement? If I
> have a relatively large dataset to initialize my database with that has
> a significant number of relationship interdependencies that require
> variable scope to be quite large, then this may not scale particularly well.
>
> Another option is to write an application (C++ and IBPP, for example)
> that does this work for me, but then anyone who is responsible for
> dealing with the default data set would have to deal with C++ instead of
> just living in the SQL world...
>
> What is the 'best practice' for this scenario?
>
> Thx!
>
> Greg
>
>
>
>
>
>
>

1.)
Modify your before insert triggers to don't use a generator value when a
value is supplied.

Supply know values for MaterID and use it on the Child table.

INSERT INTO MASTER (MasterID, DESC) VALUES (1, 'FIRST');
INSERT INTO CHILD (MASTERID, DESC)
VALUES (1, 'FIRST CHILD OF FIRST');
...


2.)

INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO CHILD (MASTERID, DESC)
VALUES ((select MasterID from Master where Desc = 'FIRST'), 'FIRST CHILD OF FIRST');
...


3.) I wouldn't use this... but it could work....
This will just work if you are running mono-user on the initial setup.

INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO CHILD (MASTERID, DESC)
VALUES (select max(MasterID) from Master, 'FIRST CHILD OF FIRST');

another variant: (very ugly by the way :-)) )
This will just work if you are running mono-user on the initial setup.
INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO CHILD (MASTERID, DESC)
VALUES (gen_id(MASTER_GEN, 0), 'FIRST CHILD OF FIRST');


4.)
I think there are lot of other options.... Use the imagination :-)

I'd go for option 1 or 2.


see you !


--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br