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:

`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 AUTONUMBERs 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 example below 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 UPDATEs, 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 code below 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

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;

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

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 code sample below 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

`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

B Code and Query Samples

AUTONUMBER Data Type Emulation