Insert record on resolving table

[Banks]

HI,

I am writing a stock management database for my dissertation.

I have 3 tables - 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.

I have a trigger on tbl_part which inserts a new part_id

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

This is the stored procedure i use to add a part to the part table

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



END;
/
show errors;

Any ideas on how i should go about inserting a record into parts_supplied. The values needed are the new part id and the supplier id which is passed through to the above procedure.

Any help is much apreciated, an example of how this is done would be fantastic, but an explanation of what to do will surfice.

Many thanks,

Alex

[LKBrwn_DBA]

1) Use a BEFORE INSERT trigger.

2) In your procedure you need to use the VALUES clause:

INSERT INTO tbl_part VALUES (s_part_no,...

3) To insert into your Parts_Supplied table, just add the INSERT statement in the same procedure.

[slimdave]

I don't like using triggers for this sort of thing -- it's a maintenance nightmare. I'd just put it in the SP_ADD_PART procedure.

[LKBrwn_DBA]

I agree with slimdave except for the possibility of bulk loading rows with sql loader (or using external tables); then you would need the trigger.

[slimdave]

You wouldn't need the trigger -- it would slow down the bulk load anyway. There would still be a better method.

[LKBrwn_DBA]

The trigger sets a column value to the next sequence number. This is the best way for bulk load's.

[slimdave]

For setting the sequence number maybe, although there are other efficient ways (using SQL*Loader's built in sequence functionality might be worth considering), but not for logging the values into the other table.

[LKBrwn_DBA]

Bank's trigger is not logging anything, just populating the seqquence.

[slimdave]

You suggested using a trigger for populating the parts_supplied table. That's what I think is a bad idea.

[LKBrwn_DBA]

Sorry I was misunderstood, but my suggestion was to replace the "AFTER INSERT" to "BEFORE INSERT" trigger for the sequence number.