Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger to set Primary Key/Foreign keys in intersect table...

422027Sep 25 2007 — edited Oct 5 2007
I have written database triggers to populate an intersecting table. I look at the one table and the data is there. I look at my intersecting table and the FK is on the data entry form, but not committed to the database. My intersect table on has columns for its PK and 2 FKs. How do you commit a record to the database in a trigger when you update a table. Example: I have a Software, Hardware and License table. The license table is the intersect between the other 2. License has a license_id(PK), software_id(FK) and hardware_id(FK) How do I write the trigger to populate the FKs? I have a Before Insert trigger on the HW and SW tables that insert into the License table, but setting the FKs doesn't seem to work. Here's both triggers:
DROP TRIGGER hardware_bir;
CREATE OR REPLACE TRIGGER hardware_bir
BEFORE INSERT
ON st_hardware_inventory
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN

IF :new.hardware_id IS NULL THEN
SELECT hardware_seq.nextval
INTO :new.hardware_id
FROM dual;

INSERT INTO st_licenses(license_id)
VALUES(license_seq.nextval);

END IF;
END hardware_bir;
/
show errors;


DROP TRIGGER license_bir;

CREATE OR REPLACE TRIGGER license_bir
BEFORE INSERT
ON st_licenses
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

DECLARE
v_hrdwre_id number(10);
BEGIN

SELECT max(hardware_id)
INTO v_hrdwre_id
FROM st_hardware_inventory;

IF :new.license_id IS NULL THEN
SELECT license_seq.nextval
INTO :new.license_id;

UPDATE st_licenses
SET hardware_id = v_hrdwre_id
WHERE hardware_id IS NULL;
end if;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 2 2007
Added on Sep 25 2007
9 comments
754 views