How to update my joining table on insert of table

[696796]

HI,

I am writing a stock management database for my dissertation.

I have 3 table - Part, Parts_Supplied and Supplier...

When i insert a part, i need to also insert a record into Parts_supplied which contains only the part_id and supplier_id.


Do i use an oracle insert trigger when the part table is updated? If so, i am unsure how to get the newly added part_id and supplier_id to this trigger. Could i do this whole procedure in one go??

I'll leave my addpart plsql

CREATE OR REPLACE  PROCEDURE SP_ADD_PART
    (s_part_no		IN	tbl_part.s_part_no%TYPE,
     s_part_Desc	IN	tbl_part.s_part_Desc%TYPE,
     d_cost		IN	tbl_part.d_cost%TYPE,
     i_qty		IN	tbl_part.i_qty%TYPE,
     i_btype_id		IN	tbl_Part.i_btype_id%TYPE,
     s_location		IN	tbl_Part.s_location%TYPE,
     i_kanban_qty	IN	tbl_Part.i_kanban_qty%TYPE,
     i_lead_time	IN	tbl_Part.i_lead_time%TYPE,
     i_low_level	IN	tbl_Part.i_low_level%TYPE
     )
IS
BEGIN

--insert order
INSERT INTO tbl_part (s_part_no, s_part_Desc, d_cost,i_qty, i_btype_id, s_location,i_kanban_qty,i_lead_time, i_low_level)VALUES (s_part_no, s_part_desc, d_cost,i_qty,i_btype_id,s_location,i_kanban_qty,i_lead_time, i_low_level);



END;
/
show errors;

ps. i can also pass through the supplier_id if required.

Many thanks, your help is much apreciated, especially as my deadline is friday!!!

Alex

 

[696796]

I have found i can do a second insert on parts_supplied. Does anyone know how i get the newly added part_id???

 

[SantaMufasa]

Is "696796" your given name or your surname? ...Or have you a more "familiar" name that you would prefer we use?

If I were in your position, I would build a trigger:

create trigger <somename>
after INSERT on PART
for each row...

Doing so, you can refer to ":new.part_id" that you can then use while inserting values to your other table(s), which INSERTs can occur in the trigger body itself, provided you do not attempt to modify the PART table.

You can query the appropriate "supplier_id" from within the trigger body, as well, for use in your INSERTs.

Let us know if this gives you the direction you desire.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[696796]

Thanks for the reply, its Alex btw ;-)

I think this is the exact direction i need to follow, but i am unsure still of how the trigger will look.

This is my trigger on tbl_part which inserts a sequential i_part_id when i add a record to the part table

CREATE OR REPLACE TRIGGER trg_part_auto 
BEFORE INSERT 
ON tbl_part 
FOR EACH ROW 
BEGIN
   SELECT seq_part_id.NEXTVAL INTO :NEW.i_part_id FROM DUAL;
END;
/

I dont see how i get the supplier_id (which is available from my form) into the trigger?!!!

Many thanks,
Al

 

[696796]

I just tried this

CREATE OR REPLACE  PROCEDURE SP_ADD_PART
    (s_part_no		IN	tbl_part.s_part_no%TYPE,
     s_part_Desc	IN	tbl_part.s_part_Desc%TYPE,
     d_cost		IN	tbl_part.d_cost%TYPE,
     i_qty		IN	tbl_part.i_qty%TYPE,
     i_btype_id		IN	tbl_Part.i_btype_id%TYPE,
     i_supplier_id	IN	tbl_Supplier.i_supplier_id%TYPE,
     s_location		IN	tbl_Part.s_location%TYPE,
     i_kanban_qty	IN	tbl_Part.i_kanban_qty%TYPE,
     i_lead_time	IN	tbl_Part.i_lead_time%TYPE,
     i_low_level	IN	tbl_Part.i_low_level%TYPE
     )
IS
BEGIN

--insert order
INSERT INTO tbl_part (s_part_no, s_part_Desc, d_cost,i_qty, i_btype_id, s_location,i_kanban_qty,i_lead_time, i_low_level)VALUES (s_part_no, s_part_desc, d_cost,i_qty,i_btype_id,s_location,i_kanban_qty,i_lead_time, i_low_level);

Insert into tbl_supplied_parts (i_part_id, i_supplier_id) Values (:new.i_part_id, i_supplier_id);


END;
/
show errors;

gettin an error of 'bad bind variable 'new.part_id'