Trigger-Assigned Primary Key from the Database Sequence

• If the primary key is assigned by a database sequence, its value is available at the posting of the entity, not the referencing one.
• During the transaction, a unique, temporary negative value is used until posting, and then refreshed with the database sequence value.
• All referencing entities are created pointing to the temporary value and need to be refreshed to prevent orphans. (Products  Suppliers)
• For composed entity objects, the child foreign keys are automatically refreshed when the parent is posted with the database sequence value.

Notes:

Trigger-Assigned Primary Key from the Database Sequence

create or replace

TRIGGER ASSIGN_SUPPLIER_ID BEFORE INSERT ON SUPPLIERS

FOR EACH ROW

BEGIN

IF :NEW.SUPPLIER_ID IS NULL OR :NEW.SUPPLIER_ID < 0 THEN

SELECT SUPPLIER_SEQ.NEXTVAL

INTO :NEW.SUPPLIER_ID

FROM DUAL;

END IF;

END;