B
Code and Query Samples

The following section contains samples that support emulation of the COUNTER data type, CASCADE UPDATE referential integrity, and name mapping queries.

COUNTER Data Type Emulation

MS Access supports a COUNTER data type. The COUNTER 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 MS Access COUNTERs and Oracle sequences is that a trigger code is required in Oracle to place a sequence number in a column when a new record is inserted into a table.

When Jet has an attachment to an Oracle table and an Oracle trigger changes or initializes the key values at the time of an insert (not UPDATEs), 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.

The example below shows how to emulate a COUNTER data type in Oracle. The Oracle table is defined as:

CREATE TABLE OTBLCOUNTERTEST(

PK NUMBER (10,0),

NAME VARCHAR2 (50),

CONSTRAINT PK_OTBLCOUNTERTEST PRIMARY KEY (PK))

An Oracle sequence is defined as:

CREATE SEQUENCE   TEST     INCREMENT BY 1    START WITH 1000 

The trigger code is indicated below:

Create Trigger TRG_CNT_OTBLCOUNTERTEST
Before INSERT OR UPDATE on OTBLCOUNTERTEST
FOR EACH ROW
DECLARE
   iCounter SCOTT.OTBLCOUNTERTEST.PRIMARYKEY%TYPE;
   cannot_change_counter EXCEPTION;

BEGIN
   IF INSERTING THEN
      SELECT TEST.NEXTVAL into iCounter FROM dual;
      :new.PRIMARYKEY := iCounter;
   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_counter;
      END IF;

   END IF; -- End of Updating Code

EXCEPTION
   WHEN cannot_change_counter THEN
      raise_application_error(-20000,'Cannot Change Counter Value');
END;

This trigger emulates the COUNTER data type by trapping both INSERT and UPDATE operations on a table. On any insert the trigger will get 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 COUNTER; if so, an exception is raised and the error is passed back to MS Access.

It is not recommended to silently protect the COUNTER on UPDATE. For example, with the following code, Jet becomes confused in its management of the dynaset and produces strange 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

As a possible enhancement to strict COUNTER field emulation, you could use the following code in the trigger to allow MS Access to pass a value for the COUNTER on a row insert:

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

This code will generate a new COUNTER value only if the passed value is NULL.

Name Mapping Query

To begin building a name mapping query in MS Access, use either the QBE or SQL window to define the query. In this example, the original MS 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 will take 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 MS 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 MS 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, which can be used to enforce table and column constraints. However, when moving an application from MS 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. Notice that <Access Validation Code> indicates where you can insert the validation code from an MS 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 are supposed to 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 will support 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. Note that 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.

Please note that 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