Myles,

At 04:35 PM 9/10/2005 +0000, Myles Wakeham wrote:

>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);

Did you know that the collation with the name matching the charset is the
default collation? That means you don't need to declare it.

>/* 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);

You should remove the above index. It already exists, having been created
for the foreign key.

>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);

Remove both of the above indexes - they already exist.

>---------------------------------------------------------------------
>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.

Not "committed", just "existing in your transaction".

To do what you want to do, you would need to be in the situation whereby,
at the time you write that PART_STOCK record, you have already created the
WAREHOUSE record (if the trigger is firing from PART) or you have already
created the PART record (if the trigger is firing from WAREHOUSE). The
problem with doing this in a trigger from either of the primitives is that
triggers can't take input parameters, so you have no way to tell the PART
trigger the warehouse id, or to tell the WAREHOUSE trigger the part id.

Let's look at the boots on these feet.

First, a trigger on the PART table to assign a WAREHOUSE location for
it. On the surface, it doesn't seem reasonable to my tiny brain that you
would want to cater for the possibility of assigning a stock item to a
warehouse that doesn't exist, except as I describe below. It's within the
bounds of possibility that you might need to create a new PART record for
an incoming good that doesn't exist in your system yet (though it rather
begs the question of how it could have been ordered...). But I can't see
how you could bring a shipment into a warehouse that you don't know about.

A lot of inventory systems have a "virtual warehouse", e.g. wh id 0, where
goods go as they are checked in from the truck - this is probably the
checkers' table.

This means that you *can* write a trigger on the PART table, to create a
record in PART_STOCK, to place the incoming good in wh id 0. Effectively,
it's "the default warehouse".

You can also store a "default warehouse" as an attribute of a part; or
your business rules might say that a particular class of part has to go
into a particular "default warehouse". If you have these attributes in
your PART record, you have some way to derive the wh id in a trigger.

However, when the boot is on the other foot - you are adding a new location
to WAREHOUSE - you would have no way to tell its triggers what parts are
going into it. So creating a PART_STOCK record via an insert trigger on
WAREHOUSE seems to be both illogical and impossible.

Back to the basic problem of referential integrity - you can certainly
create a warehouse, a part and a part-stock record all in the same
transaction. As long as you have posted the two primitives (warehouse and
part) before you attempt to insert the part_stock record, you'll be
fine. If your business rules call for a default stock record of some kind
that can be derived from the data you put into the PART record, fine, write
an After Insert trigger that uses that default and creates a PART_STOCK
record; otherwise, do the three inserts in three statements in the right
order, or via a stored procedure and let the RI triggers take care of things.

And do get rid of those duplicate indexes. They can really hurt performance.

./heLen