Associate Entities to Sequences
Sequences are database objects that the sequence generator in the database uses to generate sequential values. Your database can auto-generate unique primary key for an entity (table or view) using these sequential values. Without the presence of identity columns, your database can obtain a unique primary key value using these sequences.
lac51
Sequences
are database objects that the sequence generator in the database uses to generate sequential values. Your database can auto-generate unique primary key for an entity (table or view) using these sequential values. Without the presence of identity columns, your database can obtain a unique primary key value using these sequences.When you connect to a database that supports sequences, such as Oracle,
Layer7 Live API Creator
detects the sequences that are defined in your schema. Layer7 Live API Creator
cannot know which entity uses which sequence.For more information about the databases that support sequences, see the documentation for the specific data source.
If you are using Oracle 12c, your database can use identity columns and it can obtain a unique primary key value for the identity column using sequences. If you use this approach, you do not need to associate entities to sequences using API Creator.
For more information about how to have the Oracle database generate sequential values, see Oracle Data Source.
If your database does not use identity columns, you can generate sequential values using one of the following approaches:
- HaveLayer7 Live API Creatorauto-generate sequential values for the primary keys for the entities. With this approach, when you connect to a data source that supports sequences,Layer7 Live API Creatordetects the sequences that are defined in your schema. To implement this approach, useoneof the following methods:
Associate entity columns to sequences if
any
the following cases are true:- You wantLayer7 Live API Creatorto set the primary key for the entities.
- You wantLayer7 Live API Creatorto auto-generate numbers for the entity columns using the sequences.
Verify the Prerequisites
Before you associate entity columns to sequences, verify that your database includes sequences.
Use Triggers with Sequences
- In the database, define code that returns an auto-increment value as the primary key. The following Oracle sample code shows creating a sequence, a table, and a trigger that uses the sequence:CREATE SEQUENCE orders_seq increment by 1 start with 2000 nocacheCREATE TABLE orders ( ident number(9) PRIMARY KEY, amount_total decimal(19,4))-- create a trigger that uses the sequence CREATE OR REPLACE TRIGGER orders_trg BEFORE INSERT ON orders FOR EACH ROW WHEN (new.ident IS NULL) BEGIN SELECT orders_seq.NEXTVAL INTO :new.ident FROM dual;END;
- In API Creator, for each entity that uses triggers, associate the entity column that is set by the trigger. Complete the following steps:
- With your API open, in the Create section, clickData Sources.The Connection tab appears by default.
- Click theSequencestab.The Sequences page displays. The entities, the available columns, and the sequence names thatLayer7 Live API Creatordetects for the data source are displayed.
- Select the entity column that is set by a trigger forColumn, accept the default value (<None>) forSequence, and then save your changes by clickingSave. Accepting the default value of<None>allows the Oracle database to generate a sequential value in the trigger and assign it to the entity column.
The entity column is associated to the sequence.
Associate Entity Columns to Sequences using API Creator
- With your API open, In the Create section, clickData Sources.The Connection tab appears by default.
- Click theSequencestab.The Sequences page displays. The entities, the available columns, and the sequence names thatLayer7 Live API Creatordetects for the data source are displayed.
- For each entity that uses a sequence, select the entity column forColumn, select the sequence for the entity forSequence, and then save your changes by clickingSave.
The entity column is associated to the sequence.
Define Rules that Return a Unique Primary Key
You can have
Layer7 Live API Creator
generate the following primary keys for the table using sequences and assign it to a column by way of event rules:- Alphanumeric unique primary key:Define apre-insert event rule. Within the code for the rule, define howLayer7 Live API Creatorcomputes the key. Pre-insert event rules fire only on client inserts. Rules of this type automatically handle inserting a parent and a set of children in a single transmission (Cascade Add).TheOracle Sequence ExampleAPI sample illustrates how you can haveLayer7 Live API Creatorinsert a parent and a set of children in the same request.For more information about this example, see Oracle Sequence Example API Sample.
- Unique primary key:Define anearly event rule. Define a rule of this type for row inserts that are triggered from within your rule logic (for example, audit changes into child tables).TheOracle Sequence ExampleAPI sample illustrates how you can haveLayer7 Live API Creatorcompute an alphanumeric primary key in an early event rule using sequences.For more information about this example, see Oracle Sequence Example API Sample.
For more information about how to define pre-insert and early event rule types, see Event Rule Types.