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:
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 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 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 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
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;
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
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
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