How to Get Trigger-Assigned Primary Key Values from a Database Sequence

4.10.10 How to Get Trigger-Assigned Primary Key Values from a Database Sequence

One common case for refreshing an attribute after insert occurs when a primary key attribute value is assigned by a BEFORE INSERT FOR EACH ROW trigger. Often the trigger assigns the primary key from a database sequence using PL/SQL logic. Example 4-9 shows an example of this.

Example 4-9 PL/SQL Code Assigning a Primary Key from a Database Sequence

CREATE OR REPLACE TRIGGER ASSIGN_SVR_ID
BEFORE INSERT ON SERVICE_REQUESTS FOR EACH ROW
BEGIN
 IF :NEW.SVR_ID IS NULL OR :NEW.SVR_ID < 0 THEN
   SELECT SERVICE_REQUESTS_SEQ.NEXTVAL
     INTO :NEW.SVR_ID
     FROM DUAL;
   END IF;
END;

On the Details tab (on the Attributes page of the overview editor), you can set the value of the Type field to the built-in data type named DBSequence, and the primary key will be assigned automatically by the database sequence. Setting this data type automatically selects the Refresh on Insert checkbox.

Note:

The sequence name shown on the Sequence tab is used only at design time when you use the Create Database Tables feature described in Section 4.2.6, "How to Create Database Tables from Entity Objects." The sequence indicated here will be created along with the table on which the entity object is based.

When you create a new entity row whose primary key is a DBSequence, a unique negative number is assigned as its temporary value. This value acts as the primary key for the duration of the transaction in which it is created. If you are creating a set of interrelated entities in the same transaction, you can assign this temporary value as a foreign key value on other new, related entity rows. At transaction commit time, the entity object issues its INSERT operation using the RETURNING INTO clause to retrieve the actual database trigger-assigned primary key value. In a composition relationship, any related new entities that previously used the temporary negative value as a foreign key will get that value updated to reflect the actual new primary key of the master.
You will typically also set the Updatable property of a DBSequence-valued primary key to Never. The entity object assigns the temporary ID, and then refreshes it with the actual ID value after the INSERT operation. The end user never needs to update this value.

For information on how to implement this functionality for an association that is not a composition, see Section 4.14.7.3.3, "Associations Based on DBSequence-Valued Primary Keys."

Note:

For a metadata-driven alternative to the DBSequence approach, see Section 4.14.5, "Assigning the Primary Key Value Using an Oracle Sequence."

From:
Oracle® Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework 11g Release 2 (11.1.2.1.0)