OneData Sequence Triggers

OneData automatically creates sequence triggers when you create a column as a sequence. For the different kinds of sequences in OneData, the syntax for sequence and triggers is as follows.

Database Sequence

Assume that BRANCH_ID is the column tagged as database sequence.

CREATE SEQUENCE OD_SQ_394 INCREMENT BY 1
START WITH 6 MINVALUE 1 MAXVALUE 999999999
NOCYCLE NOCACHE NOORDER;

CREATE OR REPLACE TRIGGER OD_TR_395 BEFORE INSERT ON BRANCH
   FOR EACH ROW BEGIN select OD_SQ_394.nextval into
      :new.BRANCH_ID from dual
   ;
END
;

Optional Database Sequences

Assume that SEQUENCE is the column tagged as optional database sequence.

CREATE OR REPLACE TRIGGER OD_SQ_1701
     BEFORE INSERT on ADDRESS
    for each row
     BEGIN
       IF (:NEW.SEQUENCE< 0 or :NEW.SEQUENCE is null)
       THEN select NVL(max(SEQUENCE)+1,1) into :new.SEQUENCE
       from ADDRESS ;
         END IF;
END ;

Contiguous Sequences

Assume that CUST_TYP_ID is the column tagged as contiguous database sequence.

CREATE OR REPLACE TRIGGER OD_SQ_2848 BEFORE INSERT on ABC_CUST_TYP
for each row
   BEGIN IF (:NEW.CUST_TYP_ID< 0 OR :NEW.CUST_TYP_ID is null )
   THEN select min(CUST_TYP_ID) into :new.CUST_TYP_ID
    from (select NVL(min(CUST_TYP_ID) + 1,1) CUST_TYP_ID from ABC_CUST_TYP a
       where not exists (select * from ABC_CUST_TYP b
       where b.CUST_TYP_ID=a.CUST_TYP_ID + 1 ) UNION Select 1 CUST_TYP_ID
       from ABC_CUST_TYP where not exists (select 1 from ABC_CUST_TYP
       where CUST_TYP_ID=1));
       END IF;
END ;