12/13

# B Code and Query Samples

This appendix contains samples that support emulation of the `AUTONUMBER` data type, `CASCADE UPDATE` referential integrity, and name mapping queries. It contains the following sections:

- Section B.1, "AUTONUMBER Data Type Emulation"

- Section B.2, "Name Mapping Query"

- Section B.3, "Default Values"

- Section B.4, "Column and Table Validation"

- Section B.5, "CASCADE UPDATE Trigger Code"

## B.1 AUTONUMBER Data Type Emulation

Microsoft Access supports a `AUTONUMBER` data type. The `AUTONUMBER` data type provides a monotonically increasing sequence of long integers for a column in a native Jet DBMS file. Oracle supports sequences. Sequences generate a set of numbers that can be used in columns as unique identifiers. An important difference between Microsoft Access `AUTONUMBER`s and Oracle sequences is that a trigger is required in Oracle in order to place a sequence number in a column when a new record is inserted into a table.

When Jet has an ODBC link to an Oracle table and an Oracle trigger changes or initializes the key values at the time of an insert, Jet performs a sequence of queries to retrieve the new key value so that the inserted row can become a member of the dynaset. If Jet has trouble re-selecting the inserted row, the rows appears as `#DELETED` to the user. This does not apply to updates.

The following example shows how to emulate a `AUTONUMBER` data type in Oracle.

Oracle Table

    CREATE TABLE                      OTBLAUTONUMBERTEST( PK          NUMBER          (10,0), NAME            VARCHAR2                (50), CONSTRAINT                 PK_OTBLAUTONUMBERTEST PRIMARY KEY (PK))

Oracle Sequence

    CREATE SEQUENCE   TEST     INCREMENT BY 1    START WITH 1000

Trigger Code

    Create Trigger TRG_CNT_OTBLAUTONUMBERTEST
    Before INSERT OR UPDATE on OTBLAUTONUMBERTEST
    FOR EACH ROW
    DECLARE
       iAUTONUMBER SCOTT.OTBLAUTONUMBERTEST.PRIMARYKEY%TYPE;
       cannot_change_AUTONUMBER EXCEPTION;

    BEGIN
       IF INSERTING THEN
          SELECT TEST.NEXTVAL into iAUTONUMBER FROM dual;
          :new.PRIMARYKEY := iAUTONUMBER;
       END IF; -- End of Inserting Code

       IF UPDATING THEN
          -- Do not allow the PK to be changed.
          IF NOT(:new.PRIMARYKEY = :old.PRIMARYKEY) THEN
             RAISE cannot_change_AUTONUMBER;
          END IF;

       END IF; -- End of Updating Code

    EXCEPTION
       WHEN cannot_change_AUTONUMBER THEN
          raise_application_error(-20000,'Cannot Change AUTONUMBER Value');
    END;

This trigger emulates the `AUTONUMBER` data type by trapping both `INSERT` and `UPDATE` operations on a table. On any insert the trigger obtains the next value in the sequence TEST for the `PRIMARYKEY` column. On `UPDATE`s, the trigger checks to see if the user is trying to update the `AUTONUMBER.` If yes, an exception is raised and the error is passed back to Microsoft Access.

It is not recommended to silently protect the `AUTONUMBER` on `UPDATE`. In the following example, Jet is unable to successfully manage the dynaset and produces unpredictable results:

    IF UPDATING THEN
       -- Do not allow the PK to be changed.

       IF NOT(:new.PRIMARYKEY = :old.PRIMARYKEY) THEN
          :new.PRIMARYKEY := :old.PRIMARYKEY);
       END IF;

    END IF; -- End of Updating Code

You can restrict `AUTONUMBER` field emulation by adding code in the trigger to allow Microsoft Access to pass a value for the `AUTONUMBER` on a row insert. The following code generates a new `AUTONUMBER` value only if the passed value is `NULL`.

    IF INSERTING THEN
       IF (:new.PRIMARYKEY IS NULL) THEN
          SELECT test.NEXTVAL into iAUTONUMBER FROM dual;
          :new.PRIMARYKEY := iAUTONUMBER;
       END IF:
    END IF; -- End of Inserting Code

## B.2 Name Mapping Query

To begin building a name mapping query in Microsoft Access, use either the QBE or SQL window to define the query. In this example, the original Microsoft Access table is called SeqDateTable and is exported to Oracle as O_SEQDATETABLE. After the export, the table is attached to Jet as R_SeqDateTable.

When the following query is saved as SeqDateTable, it takes the place of the original table and complete the mapping to Oracle. The query maps the column names PRIMARYKEY, O_SEQUENCE and FIRSTDATE to PrimaryKey, Sequence and FirstDate for use by Microsoft Access.

    SELECT             NameMapper.PRIMARYKEY AS PrimaryKey, NameMapper.O_SEQUENCE AS Sequence, NameMapper.FIRSTDATE AS FirstDate FROM R_SEQDATETABLE;

## B.3 Default Values

Oracle supports declarative default values. However, when moving an application from Microsoft Access to Oracle, you may encounter situations where you need an insert trigger to support defaults. A reasonable design decision is to move all default processing to triggers to centralize the code and reduce maintenance complexity. The following code sample demonstrates supporting default values in a trigger:

    CREATE OR REPLACE TRIGGER BIU_M2
     BEFORE INSERT OR UPDATE
     ON M2
     FOR EACH ROW

       BEGIN
          IF INSERTING THEN
          /* Manage Default Values if a new value is NULL */
             IF :new.Address IS NULL THEN
                :new.Address := 'Default';
             END IF;
          END IF; -- End of Inserting Code
       END; -- Trigger BI_M2

## B.4 Column and Table Validation

Oracle supports `CHECK` statements that you can use to enforce table constraints and column constraints. However, when moving an application from Microsoft Access to Oracle, you may encounter situations where you need an insert trigger to support validation. The following code sample demonstrates supporting validation in a trigger. The `<Access Validation Code>` indicates where you can insert the validation code from a Microsoft Access application.

    CREATE OR REPLACE TRIGGER BIU_M2
     BEFORE INSERT OR UPDATE
     ON M2
     FOR EACH ROW

     BEGIN
          -- Validation Code
          IF NOT ( <Access Validation Code > ) THEN
             raise_application_error (-20000, '<Access Error Message>');
          END IF;
    END; -- Trigger BI_M2

## B.5 CASCADE UPDATE Trigger Code

Oracle does not provide direct support for `CASCADE UPDATE` referential integrity constraints. `CASCADE UPDATE` support means that when a primary key is changed, that change is made to all associated foreign keys in linked tables. `CASCADE UPDATE` is not a common design feature in applications. Primary keys should be stable, usually for the life of an application.

The following code example is based on two tables:

    create table M1 ( f1 number, f2 number, f3 number )
    create table M2 (f1 number, f2 number, f3 number )
    alter table M1 add primary key (f1)
    alter table M2 add primary key (f1)

This definition supports one-to-many cardinality. To add support for one-to-one cardinality add the following:

    alter table M1 add constraint uq_M1_001 unique (f2, f3)
    alter table M2 add constraint uq_M2_001 unique (f2, f3)

The following code implements `CASCADE UPDATE` code for the two tables, M1 and M2. This example uses two columns in the primary/foreign key relationships. This relationship is more complex than most and is used to fully illustrate the proper code.

Declarative and procedural support for referential integrity cannot coexist between two tables. To support `CASCADE UPDATE` between two tables, all declarative primary/foreign key relationships and referential integrity between the tables must be removed and supported instead with procedural code. This is outlined in the following code sample:

    CREATE OR REPLACE PACKAGE P_M1 AS
        fire_trigger boolean := TRUE;
     END P_M1;

     CREATE OR REPLACE PACKAGE P_M2 AS
        fire_trigger boolean := TRUE;
     END P_M2;

     CREATE OR REPLACE PACKAGE UQ_M1_M2 AS

     PROCEDURE cascade_update (
               o_F2    IN  number,
               o_F3    IN  number,
               n_F2    IN  number,
               n_F3    IN  number,
               bResult OUT boolean );

            PROCEDURE cascade_delete (
               F2      IN  number,
               F3      IN  number,
               bResult OUT boolean );

            FUNCTION pk_exists (
               F2      IN  number,
               F3      IN  number) RETURN boolean;

            FUNCTION fk_exists (
               F2      IN  number,
               F3      IN  number) RETURN boolean;

     END  UQ_M1_M2;

    CREATE OR REPLACE PACKAGE BODY UQ_M1_M2 AS

       /* Procedure cascade_update is called when field(s) */
       /*   F2 or                                          */
       /*   F3                                             */
       /*   are changed in table M1.                       */
       /*   The changes are cascaded in table M2           */

       PROCEDURE cascade_update (
                 o_F2    IN  number,
                 o_F3    IN  number,
                 n_F2    IN  number,
                 n_F3    IN  number,
                 bResult OUT boolean ) IS

       CURSOR d_cur (n1 number, n2 number) IS
          SELECT * FROM m2
          WHERE f2 = n1 AND f3 = n2
          FOR UPDATE of f2, f3;

       BEGIN

          FOR d_cur_rec IN d_cur ( o_F2, o_F3 )
          LOOP
             UPDATE M2 SET f2 = n_F2, f3 = n_F3
                WHERE CURRENT OF d_cur;
          END LOOP; -- Detail Record Loop

          bResult := true;

       END cascade_update;

       /* Procedure cascade_delete is called when a record */
       /*   in M1 is being deleted and associated          */
       /*   child records in M2 must also be deleted.      */

       PROCEDURE cascade_delete (
               F2      IN  number,
               F3      IN  number,
               bResult OUT boolean ) IS

       CURSOR d_cur (n1 number, n2 number) IS
          SELECT * FROM m2
          WHERE f2 = n1 AND f3 = n2
          FOR UPDATE;

       BEGIN

          FOR d_cur_rec IN d_cur ( F2, F3 )
          LOOP
             DELETE FROM M2
                WHERE CURRENT OF d_cur;
          END LOOP; -- Detail Record Loop

          bResult := true;

       END cascade_delete;

       /* Procedure pk_exists is called to determine is a given
          primary key exists in table M1                           */

       FUNCTION pk_exists (
            F2      IN  number,
            F3      IN  number) RETURN boolean IS

       l_F2        number;
       l_F3        number;
       bResult     boolean;

       CURSOR p_cur (n1 number, n2 number) IS
          SELECT F2, F3 FROM m1
          WHERE f2 = n1 AND f3 = n2;

       BEGIN
          OPEN p_cur( F2, F3 );
          FETCH p_cur INTO l_F2, l_F3;
          IF p_cur%NOTFOUND THEN
             bResult := false;
          ELSE
             bResult := true;
          END IF;

          CLOSE p_cur;

          RETURN( bResult );

       END pk_exists;

       /* Procedure pk_exists is called to determine is a given
          primary key exists in table M1                           */

       FUNCTION fk_exists (
            F2      IN  number,
            F3      IN  number) RETURN boolean IS

       l_F2         number;
       l_F3         number;
       bResult      boolean;

       CURSOR d_cur (n1 number, n2 number) IS
          SELECT F2, F3 FROM m2
          WHERE f2 = n1 AND f3 = n2;

       BEGIN
          OPEN d_cur( F2, F3 );
          FETCH d_cur INTO l_F2, l_F3;
          IF d_cur%NOTFOUND THEN
             bResult := false;
          ELSE
             bResult := true;
          END IF;

          CLOSE d_cur;

          RETURN( bResult );

       END fk_exists;

     END UQ_M1_M2;

     CREATE OR REPLACE TRIGGER AUD_M1
     AFTER UPDATE OR DELETE
     ON M1
     FOR EACH ROW

       DECLARE
       bResult_OK      BOOLEAN;
       bCascadeDeletes BOOLEAN  := TRUE;

       BEGIN

          IF UPDATING THEN
             IF (:old.F2 <> :new.F2) OR (:old.F3 <> :new.F3) THEN
                P_M2.fire_trigger := FALSE;
                UQ_M1_M2.cascade_update( :old.F2, :old.F3, :new.F2, :new.F3,
                                          bResult_OK );
                P_M2.fire_trigger := TRUE;
             END IF;
          END IF; -- End of Updating Code

          IF DELETING THEN
             IF bCascadeDeletes THEN
                UQ_M1_M2.cascade_delete( :old.F2, :old.F3, bResult_OK );
             ELSE
                IF UQ_M1_M2.fk_exists( :old.F2, :old.F3 ) THEN
                   raise_application_error( -20000, 'Rows exist in child table');
                END IF;
             END IF;
          END IF; -- End of Deleting Code

       END; -- Trigger AUD_M1

     CREATE OR REPLACE TRIGGER AIU_M2
     AFTER INSERT OR UPDATE
     ON M2
     FOR EACH ROW

       DECLARE
       bResult_OK    BOOLEAN;

       BEGIN

          IF INSERTING THEN
             IF NOT( UQ_M1_M2.pk_exists( :new.F2, :new.F3 ) ) THEN
                raise_application_error (-20000, 'No corresponding row in parent
                           table');
             END IF;
          END IF; -- End of Inserting Code

          IF ( UPDATING AND P_M2.fire_trigger ) THEN
             IF NOT( UQ_M1_M2.pk_exists( :new.F2, :new.F3 ) ) THEN
                raise_application_error (-20000, 'No corresponding row in parent table');
             END IF;
          END IF; -- End of Updating Code

       END; -- Trigger AUD_M2