At 09:40 AM 10/02/2007, you 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");
>
>
>What is the 'best practice' for this scenario?

I don't know what one would define as "best practice" for a scenario
such as this, especially as you have locked all doors by making the
foreign key non-nullable.

I can't tell what sort of data you might really want to use this for
but, with your sample, this works (syntax corrections included so you
can actually run it...DESC is a reserved word; and double-quotes are
not legal syntax for delimiting a string):

set term ^;
create generator gen_master ^
create generator gen_child ^

CREATE TABLE MASTER (
MASTERID BIGINT NOT NULL,
DESCr VARCHAR(200)) ^

CREATE TABLE CHILD (
CHILDID BIGINT NOT NULL,
MASTERID BIGINT NOT NULL,
DESCr VARCHAR(200)) ^
commit ^
alter table master
add constraint pk_master primary key(masterid) ^
commit ^
alter table child
add constraint pk_child primary key(childid) ^
add constraint fk_masterid foreign key (masterid)
references master ^
commit ^
create trigger bi_master for master
active before insert
as
begin
if (new.masterid is null) then
new.masterid = gen_id(gen_master, 1);
end ^

create trigger bi_child for child
active before insert
as
begin
if (new.childid is null) then
new.childid = gen_id(gen_child, 1);
end ^

create trigger ai_master for master
active after insert
as
begin
if (new.masterid < 4) then
begin
insert into child(masterid, descr)
values (new.masterid, 'First child of ' || new.Descr);
insert into child(masterid, descr)
values (new.masterid, 'Second child of ' || new.Descr);
insert into child(masterid, descr)
values (new.masterid, 'Third child of ' || new.Descr);
end
end ^

commit ^

INSERT INTO MASTER (DESCr) VALUES ('FIRST') ^
INSERT INTO MASTER (DESCr) VALUES ('SECOND') ^
INSERT INTO MASTER (DESCr) VALUES ('THIRD') ^
commit ^
set term ;^

./heLen