Using Generators with ODBC
Go Up to Programming Applications with Delphi or C++ Builder
Using an InterBase trigger to change the value of a primary key on a table can cause the BDE to produce a record or key deleted error message. This can be overcome by adding a generator to your trigger.
For example, when your client sends a record to the server, the primary key is NULL. Using a trigger, InterBase inserts a value into the primary key and posts the record. When the BDE tries to verify the existence of the just-inserted record, it searches for a record with a NULL primary key, which it will be unable to find. The BDE then generates a record or key deleted error message.
To get around this, do the following:
- Create a trigger similar to the following. The “if” clause checks to see whether the primary key being inserted in NULL. If so, a value is produced by the generator; if not, nothing is done to it.
Create Trigger COUNTRY_INSERT for COUNTRY active before Insert position 0 as begin if (new.Pkey is NULL) then new.Pkey = gen_id(COUNTRY_GEN,1); end
- Create a stored procedure that returns the value from the generator:
Create Procedure COUNTRY_Pkey_Gen returns (avalue INTEGER) as begin avalue = gen_id(COUNTRY_GEN,10); end
- Add a
TStoredProc
component to your Delphi or C++Builder application and associate it with theCOUNTRY_Pkey_Gen
stored procedure. - Add a
TQuery
component to your application and add the following code to theBeforePost
event:If(TQuery.state = dsinsert) then begin StoredProc1.ExecProc; TQuery.FieldByName('Pkey').AsInteger := StoredProc1.ParamByName('avalue').AsInteger; end;
This solution allows the client to retrieve the generated value from the server using a TStoredProc
component and an InterBase stored procedure. This assures that the Delphi or C++Builder client will know the primary key value when a record is posted.