How to insert the sequence value generated automatically for an Oracle sequence object available for a column when ADB is subscribing to that table.

How to insert the sequence value generated automatically for an Oracle sequence object available for a column when ADB is subscribing to that table.

book

Article ID: KB0085880

calendar_today

Updated On:

Products Versions
TIBCO ActiveMatrix BusinessWorks Plug-in for Database -
Not Applicable -

Description

Resolution:
Here are steps, which need to be followed:

Create a trigger on the subscribing table that would insert the next value from the sequence before any insert takes place for the subscribing table.

Following is the sample of trigger:
CREATE OR REPLACE TRIGGER cust_seq_bir
                       BEFORE INSERT ON cust_seq
                       REFERENCING OLD AS old NEW AS new
                       FOR EACH ROW
                       BEGIN
                         IF :new.id IS NULL THEN
                           SELECT custid.NEXTVAL
                           INTO   :new.id
                           FROM   dual;
                         END IF;
                       END;

Here, 'cust_seq' is the subscribing table and custid is the sequence.

While sending the data, if the column has no value then ADB will not have the column in the message and while subscribing, the value would be taken from the sequence.

Issue/Introduction

How to insert the sequence value generated automatically for an Oracle sequence object available for a column when ADB is subscribing to that table.