At 11:54 PM 6/09/2004 +0000, you wrote:

>I have a table with a primary key set by a generator in a trigger.
>
>If I append a record to a dataset opened with a select * I get an
>exception "Field 'x' must have a value".
>
>Presumably this is because the field value set by the trigger is
>overwritten to NULL by Delphi when it updates the field on post.

No. Delphi has no knowledge of your triggers. Delphi only knows to make
non-nullable columns REQUIRED.

>What is the correct way to avoid this?

Get the generator value BEFORE you post your insert. Firebird-aware
components usually have a mechanism to do this automatically for you, e.g.
IBO has the GeneratorLinks property and the Gen_ID() methods.

Ensure that your trigger has this essential logic:

if (new.TheValue is null) then
new.TheValue = Gen_ID(MyGen, 1);

If you are using components that don't have generator support, then write
your own Gen_ID() function and call it in BeforeInsert or conditionally in
BeforePost.

function MyDM.Gen_ID(GeneratorName: string, Increment: integer): integer;
begin
(* create a data access object, e.g. MyObject, and hook it up to your
connection component *)
with MyObject do
try
SQL.Add('select Gen_ID(' + GeneratorName + ', ' + IntToStr(Increment)
+ ') ');
SQL.Add('from rdb$database');
if not Prepared then Prepare;
Open;
Result := Fields[0];
finally
Free;
end;
end;

>Should I select explicit fields (approx 200 so I don't like this idea)

You can, but it's horrible.

>Should I set the generator value with a second query to GEN_ID()?
>Is there something I have missed?

Yup. One is that Delphi has no knowledge of triggers or generators. The
other is that generators run outside any user transaction context so,
provided you write a safe trigger, it is not necessary to wait until the
DML hits the server to get the generator value.

Another is that you're fairly crippled using the VCL and the BDE with
Firebird and InterBase.

btw, this isn't a Delphi list, and there is a list where you can post
Delphi/Firebird/BDE-related questions that won't get you attacked by
Borland extremists:

http://www.elists.org/mailman/listinfo/delphi-db

IBO and FIBPlus both have their own user lists.

./heLen