ACCESS MIGRATION

When migrating a database from Access to Oracle, and using NOT the "Quick Migration" (doing the whole thing from scratch), I notice an error (a logic one) in the generated trigger statement for simulating the auto-incremental field functionality.

Firstly, in some conditions, the triggers gets into an almost infinite loop, only stoping when reachs the upper limit of the sequence. If I'm not mistaken, it happens when the related table already has some/many records/rows on already (I don't remember).

And secondly, the original trigger logic imposes that the sequence value of 1 (one) will never happen.

Here follows the original trigger code:

CREATE OR REPLACE TRIGGER tab_14_eqdsmd1_fld_sqd1_TRG BEFORE INSERT OR UPDATE ON tab_14_eqdsmd1
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.fld_sqd1 IS NULL THEN
SELECT tab_14_eqdsmd1_fld_sqd1_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT max(fld_sqd1) INTO v_newVal FROM tab_14_eqdsmd1;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT tab_14_eqdsmd1_fld_sqd1_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
@identity
msaccess_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.fld_sqd1 := v_newVal;
END IF;
END;

/


And here is the "fixed" code for the same trigger:

CREATE OR REPLACE TRIGGER tab_14_eqdsmd1_fld_sqd1_TRG BEFORE INSERT OR UPDATE ON tab_14_eqdsmd1
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.fld_sqd1 IS NULL THEN
SELECT tab_14_eqdsmd1_fld_sqd1_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT nvl(max(fld_sqd1), 0) INTO v_newVal FROM tab_14_eqdsmd1;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN (v_incval>=v_newVal) or (v_newVal=1);
SELECT tab_14_eqdsmd1_fld_sqd1_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
@identity
msaccess_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.fld_sqd1 := v_newVal;
END IF;
END;

/

These only two changes seem to fix things and make them work as expected.

Hope you can change these for the next release. I make tens of migrations every month and fixing it manually is sometimes boring and error prone.

[]'s

Roger Reghin
Petrobras - Brazil