OneData 10.7 | Managing Master Data with webMethods OneData | Implementing webMethods OneData | Managing an Object Structure | OneData Audit Columns and Triggers | OneData Sequence Triggers
 
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 ;