Trigger-Assigned Primary Key Values from a Database Sequence
One common case where Refresh After Insert comes into play is a primary key attribute whose value is assigned by aBEFORE INSERT FOR EACH ROW trigger. Often the trigger assigns the primary key from a database sequence using PL/SQL logic similar to this:
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;Set the Attribute Type to the built-in datatype named
DBSequence, as shown in Figure 6-14, and the primary key will be assigned automatically by the database sequence. Setting this datatype automatically enables the Refresh After Insert property.
When you create a new entity row whose primary key is a
DBSequence,
a unique negative number gets 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. 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.
Note:
As shown in Figure 6-14, 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 option. The end user never needs to
update this value.
|
Figure 6-14 Setting Primary Key Attribute to DBSequence Type Automates Trigger-Assigned Key Handling
