ADF DBSequence for table using trigger and sequence
In some database schema, we may have enabled a database ‘Before Insert Trigger’ for a table which inserts an auto-generated sequence number in a primary key field when an inserts happen. When we create an Entity Object for the same table in ADF and insert a record, it will throw an error for the Primary key field cannot be empty/null. To solve this, Open the EO, select the primary key field, change type to DBSequence as shown below. After this, ADF EO uses the database trigger to insert primary key value
The above section is detailed in the following example.
ADF DBSequence Example:
For example, I have created the following table(Sample) in my HR schema. The column ‘Sample_key’ is primary key attribute
Created the following sequence for the table. It generates a unique key for column ‘Sample_key’
CREATE SEQUENCE "HR"."SAMPLE_SEQ" MINVALUE 1 MAXVALUE 1000 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ;
Created the following Trigger. It uses the above sequence and inserts the sequence value in primary key field
create or replace TRIGGER SAMPLE_SEQ_TRIGGER
BEFORE INSERT ON HR.SAMPLE
FOR EACH ROW
IF :new.SAMPLE_KEY IS NULL THEN
SELECT SAMPLE_SEQ.nextval INTO :new.SAMPLE_KEY FROM DUAL;
When I created EO for the table ‘Sample’ in ADF. I changed the primary key type to DBSequence
Test ADF DBSequnce:
Right click on the EO and select Run. Click the green plus icon on the wizard to insert a record. ADF puts a negative value in the primary key field. When commit happens ADF leave it to database trigger to insert value.
The primary key value of the inserted record
The views expressed on this blog are my personal views and do not necessarily reflect the views of my employer.
Please feeling free to reach me on any comments and feedbacks you have. Would be more than glad to listen and reply 🙂
Latest posts by Govind
- Is it possible to logout Primavera P6 through SQL Query? - February 16, 2018
- How to Create Primavera P6 EPPM Database schemas in Microsoft SQL Server - February 15, 2018
- Configure Windows Firewall for Microsoft SQL Server 2017 – Part 4 - February 14, 2018