--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> Myles, this description is too vague and ambiguous. Provide the key
> structures of the two tables, together with the definitions of the keys.

OK. Here there are three tables involved: Part, Warehouse and
Part_Stock. Parts reside in multiple warehouses, and a warehouse can
have many parts. Therefore the Part_Stock table resolves this many to
many relationship. Here are the details:

/* Table: PART */

CREATE TABLE PART (
PART_ID INTEGER NOT NULL,
PART_CLASS VARCHAR (20) CHARACTER SET WIN1251 NOT NULL COLLATE
WIN1251,
PART_DESC CHAR (100) CHARACTER SET WIN1251 NOT NULL COLLATE WIN1251,
PART_UOM CHAR (20) CHARACTER SET WIN1251 COLLATE WIN1251,
PART_TAXABLE CHAR (1) CHARACTER SET WIN1251 Default 'N' COLLATE
WIN1251,
PART_VEN_DESC CHAR (100) CHARACTER SET WIN1251 COLLATE WIN1251,
PART_ACCT_SALES VARCHAR (100) CHARACTER SET WIN1251 COLLATE WIN1251,
PART_ACCT_ASSETINV VARCHAR (100) CHARACTER SET WIN1251 COLLATE
WIN1251,
PART_ACCT_COGS VARCHAR (100) CHARACTER SET WIN1251 COLLATE WIN1251,
PART_SORT VARCHAR (4) CHARACTER SET WIN1251 COLLATE WIN1251,
PART_LIFO_COST NUMERIC (9, 2),
PART_FIFO_COST NUMERIC (9, 2),
PART_INACTIVE CHAR (1) CHARACTER SET WIN1251 Default 'N' COLLATE
WIN1251,
PART_TYPE_ID INTEGER NOT NULL,
PART_ITEM_NO NAME_TEXT NOT NULL);



/* Primary keys definition */

ALTER TABLE PART ADD PRIMARY KEY (PART_ID);


/* Foreign keys definition */

ALTER TABLE PART ADD CONSTRAINT FK_PART__PART_TYPE_ID FOREIGN KEY
(PART_TYPE_ID) REFERENCES PART_TYPE (PART_TYPE_ID);


/* Indices definition */

CREATE INDEX FK_PART__PART_TYPE_ID ON PART (PART_TYPE_ID);
CREATE UNIQUE INDEX IDX_PART ON PART (PART_ITEM_NO);
------------------------------------------------------------------------

/* Table: WAREHOUSE */

CREATE TABLE WAREHOUSE (
WH_ID ID,
WH_DESC NAME_TEXT NOT NULL);

/* Primary keys definition */

ALTER TABLE WAREHOUSE ADD CONSTRAINT PK_WAREHOUSE PRIMARY KEY (WH_ID);
-----------------------------------------------------------------------
/* Table: PART_STOCK */

CREATE TABLE PART_STOCK (
PS_ID ID,
ON_HAND NUMERIC (12, 4),
PS_WH_ID INTEGER NOT NULL,
PS_PART_ID INTEGER NOT NULL);



/* Primary keys definition */

ALTER TABLE PART_STOCK ADD CONSTRAINT PK_PART_STOCK PRIMARY KEY (PS_ID);


/* Foreign keys definition */

ALTER TABLE PART_STOCK ADD CONSTRAINT FK_PART_STOCK__PART_ID FOREIGN
KEY (PS_PART_ID) REFERENCES PART (PART_ID);
ALTER TABLE PART_STOCK ADD CONSTRAINT FK_PART_STOCK__WH_ID FOREIGN KEY
(PS_WH_ID) REFERENCES WAREHOUSE (WH_ID);


/* Indices definition */

CREATE INDEX FK_PART_STOCK__PART_ID ON PART_STOCK (PS_PART_ID);
CREATE INDEX FK_PART_STOCK__WH_ID ON PART_STOCK (PS_WH_ID);

---------------------------------------------------------------------
I want to add a trigger to the Part and Warehouse tables so that when
a record is inserted into either of these tables, a set of Part_Stock
records are created representing the relationship between the Part and
the Warehouses. But when I do this, I get the referential integrity
error I explained before. I started with the Warehouse table on this,
and it would appear that since its trying to create these PART_STOCK
records that reference the ID of the new warehouse, and since I'm
doing this in a trigger before the data has committed, it won't work
because the RI rule requires that the Warehouse ID already be stored.

Myles