The following example illustrates how Scriptoria replicates Access Auto-Number (Counter) fields in Oracle.
CREATE TABLE AUTO_NUMBER_EXAMPLE
(
SEQ_NUMBER
NUMBER(10)
PRIMARY KEY,
GROUP_NUMBER
NUMBER(3)
)
/
CREATE SEQUENCE SEQ_AUTO_NUMBER_EXAMPLE START WITH 1
/
CREATE OR REPLACE TRIGGER BIU_AUTO_NUMBER_EXAMPLE
BEFORE INSERT OR UPDATE OF SEQ_NUMBER
ON AUTO_NUMBER_EXAMPLE
FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT SEQ_AUTO_NUMBER_EXAMPLE.NextVal INTO :new.SEQ_NUMBER FROM DUAL;
ELSIF UPDATING THEN
:new.SEQ_NUMBER:= :old.SEQ_NUMBER;
END IF;
END;
/
The above Trigger obtains the next sequence number when a row is inserted into the AUTO_NUMBER_EXAMPLE table. Since Access Counters are non-modifiable, if a user attempts to update an existing sequence, the Trigger substitutes the original value. Note that Oracle Sequences are session independent, so multiple users may perform insert operations simultaneously without duplicating sequence numbers.
It is important to point-out another ODBC idiosyncrasy when using Sequences to uniquely identify table rows. We can insert a row into AUTO_NUMBER_EXAMPLE table by leaving the SEQ_NUMBER field blank and entering a value (e.g., 1) in the GROUP_NUMBER field. After saving the row, Access returns the correct SEQ_NUMBER value of 1. However, if we attempt to insert another row into the AUTO_NUMBER_EXAMPLE table after specifying the same value for the GROUP_NUMBER field, a row with #DELETED# in both fields is returned. The #DELETED# message occurs because Access/ODBC cannot determine the uniqueness of the record. In order for Access to update or delete an existing record, it must identify a unique set of field values for each record. It does this by concatenating all valued non-Memo/OLE Object fields and testing their uniqueness. If it cannot establish uniqueness, this misleading message is issued.
So why doesn’t the Oracle sequence establish uniqueness? When using a trigger to generate an Auto-Number like value, the Oracle row reflects the new value, but since the trigger fires on the Oracle server, Access does not know the value until the row is re-read. When Access tries to identify uniqueness, the Auto-Number field is still null and therefore not included in the uniqueness test.
To work around this problem, you can add a date field to the record and set it to Now() on your form (or enter a unique date in data sheet view.) Note that, using Now() as a Default Value will not work since Oracle defaults are also unknown to Access until the record is re-read.