-
Insert record on resolving table
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
Code:
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
Code:
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
-
1) Use a BEFORE INSERT trigger.
2) In your procedure you need to use the VALUES clause:
Code:
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.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
-
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.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
You wouldn't need the trigger -- it would slow down the bulk load anyway. There would still be a better method.
-
The trigger sets a column value to the next sequence number. This is the best way for bulk load's.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
-
Bank's trigger is not logging anything, just populating the seqquence.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
You suggested using a trigger for populating the parts_supplied table. That's what I think is a bad idea.
-
Sorry I was misunderstood, but my suggestion was to replace the "AFTER INSERT" to "BEFORE INSERT" trigger for the sequence number.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|