Automatic Key Field Value Generation

When editing dataset it is often convenient to generate key field(s) values automatically instead of filling them manually. In the most common way application developer generates primary key value basing it on previously created sequence. There are three ways to do it.

First, application independent way - developer creates AFTER INSERT trigger that fills the field value. But here he faces the problem with getting value inserted by trigger back to dataset. This problem can be easily solved in ODAC by specifying return parameters. For instance:

...
// suppose that AFTER INSERT trigger fills DepNo field
OraQuery.SQL.Text := 'SELECT DepNo, DepName, Location FROM Department';
OraQuery.SQLInsert.Text := 'INSERT INTO Department (DepNo, DepName, Location)' +
                           'VALUES(DepNo, DepName, Location) ' +
                           'RETURNING DepNo INTO :DepNo';
...

Second way is custom key field value generation. Developer can fill key field value in TOraDataSet.BeforePost event handler. But in this case he should manually execute query and retrieve sequence value. So this way may be useful only if some special value processing is needed.

The third way, using KeySequence is the simplest. Developer only needs to specify two properties and key field values are generated automatically. There is no need to create trigger or perform custom BeforePost processing.

...
OraQuery.SQL.Text := 'SELECT DepNo, DepName, Location FROM Department';
OraQuery.KeyFields := 'DepNo';         // key field
OraQuery.KeySequence := 'DepSequence'; // sequence that will generate values
...

See also

• KeySequence