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

090716_0041_ADFDBSequen1 ADF DBSequence  for table using trigger and sequence

 

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

090716_0041_ADFDBSequen2 ADF DBSequence  for table using trigger and sequence

Created the following sequence for the table. It generates a unique key for column ‘Sample_key’

 

Created the following Trigger. It uses the above sequence and inserts the sequence value in primary key field

 

When I created EO for the table ‘Sample’ in ADF. I changed the primary key type to DBSequence

090716_0041_ADFDBSequen3 ADF DBSequence  for table using trigger and sequence

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.

090716_0041_ADFDBSequen4 ADF DBSequence  for table using trigger and sequence

The primary key value of the inserted record

090716_0041_ADFDBSequen5 ADF DBSequence  for table using trigger and sequence

govindan ADF DBSequence  for table using trigger and sequence
Connect me

Govind

Thank you for visiting my personal blog. Myself Govindan, Software Developer by profession since 2006 and hence I started this blog early in 2016 and ever since I've been writing about technologies experienced and learnings of everyday life.

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 🙂
govindan ADF DBSequence  for table using trigger and sequence
Connect me
By | 2016-09-06T19:50:42+00:00 September 6th, 2016|Categories: ADF|Tags: , , |0 Comments
Like us on Facebook.
Connect!