APEXtras

a development team dedicated to Oracle APEX

Posts Tagged ‘trigger

Automatic Oracle Sequence Regeneration

with one comment

Most of our Oracle tables have a surrogate primary key (SPK) whose value is derived in the normal way from an Oracle sequence and inserted via a trigger tied to the “BEFORE INSERT” condition.

It takes some effort to maintain all the sequences and triggers. For every new table an associated sequence and trigger must be created, and if any sequence or trigger is accidentally dropped during development the development system will eventually fail in an unobvious way. We also frequently copy table data between systems – eg from the live to the staging system, so that candidate releases can be tested on live data. When data is copied, all the relevant sequences must be reset to be higher than the new maximum SPK value.

We therefore developed a PL/SQL function ResetSequences provided as part of an Oracle package that would

  • Reset the current value of each sequence to be the maximum value of the SPK in the associated table
  • Detect and recreate a missing sequence or trigger (or both)

ResetSequences takes as parameters the name of the tablespace in which to operate and either a table prefix which all affected tables should match or a comma separated list of table names. If both the table prefix and list of names are null, it attempts to reset sequences for every table in the given tablespace.

ResetSequences returns as a CLOB a text list of what sequences it reset or created, and what triggers it created.

It can be called from SQL like this:
SELECT APEXTRAS_RESET_TRIG_SEQ.ResetSequences(‘TABLESPACE’,’TABLE_PREFIX’,’TABLE_LIST’) FROM dual

Or from a PL/SQL procedure:

DECLARE
  l_return CLOB;
BEGIN
    l_return := APEXTRAS_RESET_TRIG_SEQ.ResetSequences(p_tablespace  => 'TABLESPACE',
                                                       p_tableprefix => 'TABLE_PREFIX',
                                                       p_tablelist   => 'TABLE_LIST');
END;

Tested with Oracle 10.2g.

The usual caveats apply about using ResetSequences on a production system – test extensively first. In particular, ResetSequences assumes that any primary key on a numeric column should be derived from a sequence and associated trigger and will create both if they don’t exist. Don’t use it on tables for which that is not true.

Technical Details

The Oracle views all_constraints, all_ind_columns, all_triggers, all_trigger_cols and all_dependencies contain the required information and when joined and filtered give us a list of tables, primary key columns, sequences, and triggers.

all_constraints filtered on all_constraints.constraint_type = ‘P’ gives the name of tables with primary keys.
all_ind_columns gives the referenced column name.
all_dependencies, suitably filtered, gives the names of the trigger and sequence.

If the trigger and sequence exist then the next value of the sequence is reset to be 1 greater than the current maximum value of the SPK column (the sequence is not dropped and recreated). If either or both of the sequence or trigger are missing, they are created from scratch.

The name given to newly created sequences and triggers is based on the table name minus the table prefix concatenated with the SPK column name. If the table and column names start with more than 3 characters in common then only the column name is used.

Eg for a table PREFIX_TEST_TABLE with SPK column TEST_TABLE_ID, the trigger and sequence would both be called TEST_TABLE_ID.
For a table PREFIX_TEST_TABLE with SPK column TAB_ID the trigger and sequence would both be called TEST_TABLE_TAB_ID.

DDL issued by ResetSequences

The parts of the DDL statements inside square brackets ([]) are each replaced by the relevant parameter or calculated number before execution.

Sequence Creation

CREATE SEQUENCE [TABLESPACE.SEQUENCE_NAME]
START WITH [START_VALUE]
INCREMENT BY 1
CACHE 20

Trigger Creation

CREATE OR REPLACE TRIGGER [TABLESPACE.TRIGGER_NAME]
BEFORE INSERT ON [TABLE_NAME]
FOR EACH ROW
WHEN (NEW.[COLUMN_NAME] IS NULL) BEGIN
  SELECT [SEQUENCE_NAME.NEXTVAL] INTO :NEW.[COLUMN_NAME] FROM DUAL;
END;

Sequence Resetting
This must be done with a series of commands:

ALTER SEQUENCE [TABLESPACE.SEQUENCE_NAME] INCREMENT BY 1 NOCACHE;
SELECT [TABLESPACE.SEQUENCE_NAME.NEXTVAL] FROM DUAL INTO nextval;
IF (nextval < desired_value) THEN
  IF (nextval < (desired_value - 1)) THEN
    ALTER SEQUENCE [TABLESPACE.SEQUENCE_NAME] INCREMENT BY [desired_value - nextval - 1] MINVALUE 1
    SELECT [TABLESPACE.SEQUENCE_NAME.NEXTVAL] FROM DUAL INTO nextval;
    ALTER SEQUENCE [TABLESPACE.SEQUENCE_NAME] INCREMENT BY 1';
  ELSIF (nextval = (desired_value - 1)) THEN
    SELECT [TABLESPACE.SEQUENCE_NAME.NEXTVAL] FROM DUAL INTO nextval;
  END IF;
END IF;
ALTER SEQUENCE [TABLESPACE.SEQUENCE_NAME] INCREMENT BY 1 CACHE [ORIGINAL_CACHE_SIZE];
Advertisements

Written by Roger

4 June, 2009 at 5:12 pm