IBUpdateSQL - primary key

[Nicole]

There is a table with a primary key which is generated within Firebird by a generator.
Works fine.

If I want to insert one row by the DBNavigaor "+" sign and a DBGrid, - I see an error message, because something like "invalid primary key".

What can I do, that my DBGrid / DBNavigator just writes new rows into my database and let the generator within firebird do his job to generate a new ID?

[rvk]

If I want to insert one row by the DBNavigaor "+" sign and a DBGrid, - I see an error message, because something like "invalid primary key".

What can I do, that my DBGrid / DBNavigator just writes new rows into my database and let the generator within firebird do his job to generate a new ID?

If you want the ID to be generated automatically, then don't put that field in the TDBGrid.
When the ID isn't used, it will be automatically generated.

But depending on your generator you could also let the new value generate on NULL or 0.
For NULL you would need to set the required property of the field to false.
That all depends on how your trigger looks.

Mine is this for LAND. So it will create a new ID for NULL but also for 0.
Anything else will just be accepted.

Code: SQL  [Select][+][-]

1. CREATE TRIGGER TR_LAND FOR LAND ACTIVE BEFORE INSERT POSITION 0 AS
2. BEGIN
3.   IF ((NEW.LINKNUMMER IS NULL) OR (NEW.LINKNUMMER = 0)) THEN
4.       NEW.LINKNUMMER = GEN_ID(GEN_LAND, 1);
5. END^

For NULL I also need to do this if I have the primary key in the SELECT and INSERT:

Code: Pascal  [Select][+][-]

1. IBQuery1.FieldByName('LINKNUMMER').Required := false

otherwise you get an error that the field is NULL and needs to have a value because it's a primary key.

If I want to insert one row by the DBNavigaor "+" sign and a DBGrid, - I see an error message, because something like "invalid primary key".

BTW. PLEASE don't mention errors like "something like".
ALWAYS state the EXACT error message !!!!

[Nicole]

Thank you for the answer. This code, where to write it?

Code: Pascal  [Select][+][-]

1.     CREATE TRIGGER TR_LAND FOR LAND ACTIVE BEFORE INSERT POSITION 0 AS
2.     BEGIN
3.       IF ((NEW.LINKNUMMER IS NULL) OR (NEW.LINKNUMMER = 0)) THEN
4.           NEW.LINKNUMMER = GEN_ID(GEN_LAND, 1);
5.     END^

This id - I need it for changing rows = asset-fields.

So one part of my form are the group of panels allowing to edit the values of one row.
Then there is a dynmicDBGrid, which allows to find items and to load them into the panel-system.
I have vage ideas, that the user can click at lines in the Grid, I will find the id by adressing the field in the query and passing the id back to the panel-group, which can be reloaded for the very id = chosen asset in grid

[rvk]

Thank you for the answer. This code, where to write it?

This is an example of a trigger in my database.

You have similar triggers in yours.
But since I have no idea about your database I can't say if it's correct.

And you didn't mention the exact error message so it was just a guess as to what the problem is.

[tonyw]

There is a table with a primary key which is generated within Firebird by a generator.
Works fine.

If I want to insert one row by the DBNavigaor "+" sign and a DBGrid, - I see an error message, because something like "invalid primary key".

What can I do, that my DBGrid / DBNavigator just writes new rows into my database and let the generator within firebird do his job to generate a new ID?

You should add a "returning" clause to the TIBUpdateSQL insert statement. You should specify that the returned field is the primary key. When you post the update, the primary key field is then automatically set to the value generated by the trigger.

If that doesn't work i.e. you need the primary key value before calling post, then you should not use a trigger. Instead, use the generatorfield property of TIBQuery. IBX will then invoke your generator as soon you click on the navigator's + button.