How to create a trigger to allow both manual and auto primary key in sql developer with oracle 11g

Question

I am attempting to create a trigger (using SQL Developer with Oracle 11g) that will allow manual insertions onto the primary key, and if a record is created without a specified primary key it will assign one from a sequence. First I tried to use a select statement in the trigger that checks if the id generated by the sequence is already in the table because of manual insertion :

DROP TABLE testing;
DROP SEQUENCE testing_seq;

CREATE TABLE testing (
id_number NUMBER PRIMARY KEY,
test_data VARCHAR(50)
);

CREATE SEQUENCE testing_seq
MINVALUE 1
MAXVALUE 10000
START WITH 1
INCREMENT BY 1
NOORDER
NOCYCLE;

CREATE OR REPLACE TRIGGER auto_testing_id BEFORE
  INSERT ON testing 
  FOR EACH ROW 
  DECLARE
    tmp NUMBER;
    seq NUMBER;
  BEGIN
    IF :NEW.id_number IS NULL THEN
      seq := testing_seq.nextval;
      SELECT
        (SELECT 1 FROM testing WHERE id_number = seq
        ) INTO tmp FROM dual;
      while (tmp = 1)
      loop
        seq := testing_seq.nextval;
        SELECT
          (SELECT 1 FROM testing WHERE id_number = seq
          ) INTO tmp FROM dual;
      END loop;
      :NEW.id_number := seq;
    END IF;
  END;
  /

INSERT INTO testing VALUES(1,'test1');
INSERT INTO testing (test_data) VALUES('test2');

SELECT * FROM testing;

Table TESTING dropped.


Sequence TESTING_SEQ dropped.

Table TESTING created.


Sequence TESTING_SEQ created.

Trigger AUTO_TESTING_ID compiled

1 row inserted.


1 row inserted.


 ID_NUMBER TEST_DATA                                        
---------- --------------------------------------------------
         1 test1                                             
         2 test2                                             

This works for manually created insertions, but not if I try to insert using a select statement. I believe this is because I am referencing the table being inserted on inside the trigger.

I tried a trigger without the check, but as expected if the trigger created an id that was already in the table it threw a unique constraint error

CREATE OR REPLACE TRIGGER auto_testing_id2 BEFORE
  INSERT ON testing 
  FOR EACH ROW 
  DECLARE
  BEGIN
      IF :NEW.id_number is null
      then 
        :NEW.id_number := testing_seq.nextval;
      end if;
  end;
/


Trigger AUTO_TESTING_ID2 compiled


1 row inserted.


Error starting at line : 59 in command -
INSERT INTO testing (test_data) VALUES('test2')
Error report -
SQL Error: ORA-00001: unique constraint (KATRINA_LEARNING.SYS_C001190313) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

 ID_NUMBER TEST_DATA                                        
---------- --------------------------------------------------
         1 test1    

I tried to catch this error (using error name DUP_VAL_ON_INDEX), and then loop it until it found the next number in the sequence that isn't in the table (with and without error catching), but it wouldn't even send up a test error message, and when I added the loop it wouldn't compile...

Can anyone please help me create a trigger that works without using a select statement to see if the sequence nextval is already used?

Answer 1

The example I'm adding here is clunky and performance here would be poor, but I wanted to put out an idea that could perhaps be a starting place.

You mentioned that you don't want to SELECT to check whether NEXTVAL is already used. If you meant that you didn't want to have to perform any SELECT at all, then this answer would be cheating as it does include a SELECT statement. But if you only want to avoid the mutating-table problem, then it could be a possibility.

The approach I'll add here takes a few steps to avoid collision, and runs them anytime a non-NULL value is provided as a key. It is currently set up to fire per-row, but if entire statements will be all-NULL or all-non-NULL then it could possible be changed to a statement- or compound-trigger to improve efficiency a little. In any event, the collision-avoidance is inefficient in this example.

General steps:
- If NULL, just use the NEXTVAL
- If non-NULL, check the LAST_VALUE and CACHE for the SEQUENCE against the provided value.
- If the provided value is within the CACHE (or beyond the cache) and could cause a collision, jump the SEQUENCE beyond the provided value and throw away the values in the cache.

Create the test table/sequence:

CREATE TABLE MY_TABLE (
  MY_TABLE_ID NUMBER NOT NULL PRIMARY KEY
); 

--Default cache here 20
CREATE SEQUENCE MY_SEQUENCE;

Create an autonomous synchronizer. Please note, this is not at all efficient, and concurrency could be a real problem (A serializing alternative is below).
It assumes a CACHE of at least 1 and may be incompatible with NOCACHE. (Actually the whole situation might be simpler with a NOCACHE SEQUENCE though)

CREATE OR REPLACE PROCEDURE SYNC_MY_SEQUENCE(P_CANDIDATE_MAX_VALUE IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
  V_LAST_NUMBER NUMBER;
  V_CACHE_SIZE  NUMBER;
  V_SEQUENCE_GAP NUMBER;
  V_SEQUENCE_DELTA NUMBER;
  V_NEXTVAL NUMBER;
  BEGIN

    SELECT
      LAST_NUMBER,
      CACHE_SIZE
    INTO V_LAST_NUMBER, V_CACHE_SIZE
    FROM USER_SEQUENCES
    WHERE SEQUENCE_NAME = 'MY_SEQUENCE';

    --Only do anything if the provided value could cause a collision.
    IF P_CANDIDATE_MAX_VALUE >= (V_LAST_NUMBER - V_CACHE_SIZE)
      THEN
        -- Get the delta, in case the provided value is way way higher than the SEQUENCE
        V_SEQUENCE_DELTA := P_CANDIDATE_MAX_VALUE + V_CACHE_SIZE - V_LAST_NUMBER ;

        -- Use the biggest gap to get a safe zone when resetting the SEQUENCE
        V_SEQUENCE_GAP := GREATEST(V_SEQUENCE_DELTA, V_CACHE_SIZE);

        -- Set the increment so the distance between the last_value and the safe zone can be moved in one jump
      EXECUTE IMMEDIATE 'ALTER SEQUENCE MY_SEQUENCE INCREMENT BY '||V_SEQUENCE_GAP;
        -- Jump to the safe zone.
        V_NEXTVAL := MY_SEQUENCE.NEXTVAL;
        -- Reset increment.  Note there is a space here that other sessions could get big NEXTVALs from concurrent access
      EXECUTE IMMEDIATE 'ALTER SEQUENCE MY_SEQUENCE INCREMENT BY 1';

        --Chew through the rest of at least one cache cycle.
        FOR CACHE_POINTER IN 1..V_CACHE_SIZE LOOP
          V_NEXTVAL := MY_SEQUENCE.NEXTVAL;
        END LOOP;

    END IF;

  COMMIT;

  END;
/

EDIT: it would be even more costly, but one might be able to serialize access to manage concurrency with something like the below alternative:

CREATE OR REPLACE PROCEDURE SYNC_MY_SEQUENCE(P_CANDIDATE_MAX_VALUE IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
  V_LAST_NUMBER NUMBER;
  V_CACHE_SIZE  NUMBER;
  V_SEQUENCE_GAP NUMBER;
  V_SEQUENCE_DELTA NUMBER;
  V_NEXTVAL NUMBER;
  V_LOCK_STATUS NUMBER;
  V_LOCK_HANDLE VARCHAR2(64);
  C_LOCK_KEY CONSTANT VARCHAR2(20) := 'SYNC_MY_SEQUENCE';

  BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE (C_LOCK_KEY,V_LOCK_HANDLE,10);
  --Serialize access
  V_LOCK_STATUS := DBMS_LOCK.REQUEST(
  LOCKHANDLE => V_LOCK_HANDLE,
  LOCKMODE => DBMS_LOCK.X_MODE,
  TIMEOUT => 10,
  RELEASE_ON_COMMIT => TRUE);

    SELECT
      LAST_NUMBER,
      CACHE_SIZE
    INTO V_LAST_NUMBER, V_CACHE_SIZE
    FROM USER_SEQUENCES
    WHERE SEQUENCE_NAME = 'MY_SEQUENCE';

    IF P_CANDIDATE_MAX_VALUE >= (V_LAST_NUMBER - V_CACHE_SIZE)
    THEN
      V_SEQUENCE_DELTA := P_CANDIDATE_MAX_VALUE + V_CACHE_SIZE - V_LAST_NUMBER ;

      V_SEQUENCE_GAP := GREATEST(V_SEQUENCE_DELTA, V_CACHE_SIZE);

      EXECUTE IMMEDIATE 'ALTER SEQUENCE MY_SEQUENCE INCREMENT BY '||V_SEQUENCE_GAP;

      V_NEXTVAL := MY_SEQUENCE.NEXTVAL;
      EXECUTE IMMEDIATE 'ALTER SEQUENCE MY_SEQUENCE INCREMENT BY 1';

      FOR CACHE_POINTER IN 1..V_CACHE_SIZE LOOP
        V_NEXTVAL := MY_SEQUENCE.NEXTVAL;
      END LOOP;

    END IF;

    COMMIT;

  END;
/

Create the trigger:

CREATE OR REPLACE TRIGGER MAYBE_SET
BEFORE INSERT ON MY_TABLE
FOR EACH ROW
  BEGIN
    IF :NEW.MY_TABLE_ID IS NULL
    THEN
      :NEW.MY_TABLE_ID := MY_SEQUENCE.NEXTVAL;
      ELSE
      SYNC_MY_SEQUENCE(:NEW.MY_TABLE_ID);
    END IF;

  END;
/

Then test it:

INSERT INTO MY_TABLE SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5;
SELECT * FROM MY_TABLE ORDER BY 1 ASC;

MY_TABLE_ID  
1            
2            
3            
4          

It just used the NEXTVAL each time.

Then add a collidable value. Adding this will fire the sproc and do the extra work to push the SEQUENCE into a safe zone.

INSERT INTO MY_TABLE VALUES(5);
SELECT * FROM MY_TABLE ORDER BY 1 ASC;

MY_TABLE_ID  
1            
2            
3            
4            
5       

Then use NULL again:

INSERT INTO MY_TABLE VALUES(NULL);
SELECT * FROM MY_TABLE ORDER BY 1 ASC;

MY_TABLE_ID  
1            
2            
3            
4            
5            
41           

The SEQUENCE had a costly operation to get there, but has settled and didn't collide.

If other provided values are below the SEQUENCE visibility, they add freely and don't change the NEXTVAL:

INSERT INTO MY_TABLE VALUES(7);
INSERT INTO MY_TABLE VALUES(19);
INSERT INTO MY_TABLE VALUES(-9999);
INSERT INTO MY_TABLE VALUES(NULL);
SELECT * FROM MY_TABLE ORDER BY 1 ASC;

MY_TABLE_ID  
-9999        
1            
2            
3            
4            
5            
7            
19           
41           
42  

If the gap is huge, it jumps way out there:

INSERT INTO MY_TABLE VALUES(50000);
INSERT INTO MY_TABLE VALUES(NULL);
SELECT * FROM MY_TABLE ORDER BY 1 ASC;

MY_TABLE_ID  
-9999        
1            
2            
3            
4            
5            
7            
19           
41           
42           
50000        
50022        

This could be too costly for your use case, and I haven't tested in in a RAC, but wanted to throw out an idea that can avoid collisions.