Inventory and history table - insert update multiple records

[robsuttonjr]

I have 2 tables.

Table1: Inventory_Location
fields: Location_ID, Item_ID, Quantity_On_Hand

Table2: Inventory_History
Fields: Location_ID, Item_ID, Quantity, GL_Number

I need to insert records into the Inventory_History then
have a trigger insert/update into Inventory_Location
(the trigger would have to update records found in Inventory_Location or insert new records). Based on this can someone suggest a trigger that would accomplish this or should I be doing this in a stored procedure? My reasoning for a trigger is so if I have to directly insert into the inventory_history I will always have inventory_location updated correctly. Thanks!!

Forums rule, pass it on!!!

Rob

 

[mrdenny]

It is much easier to do this within a stored procedure.

You could also do this with two triggers on the Inventory_Location table. One for Insert on for Update.


Denny

--Anything is possible. All it takes is a little research. (Me)

 

[robsuttonjr]

Thanks I have created a trigger for update ok but on insert I am having a problem.

If you have say location and Item
You want to allow the item to be in multiple locations
BUT never the same item in the same location.
So I created a primary key based on:
sample_key and location_key which does not allow
the same item to be in the same location.
Is this good practice? What do others do in this
situation? What is the purpose of allowing
a primary key to be made using 2 fields?
Should I be creating an index manually to manage this?



Forums rule, pass it on!!!

Rob

 

[mrdenny]

You could use either a Primary Key to do this, or a unique index if you already have a primiary key defined on another column(s).

Denny

--Anything is possible. All it takes is a little research. (Me)