Dear friends,

Following Helen's example in the Great Book, I have defined a table with a
Before Insert trigger thus:

CREATE TABLE ORGANIZATION(
CLIENT_NUMBER INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(64) NOT NULL,
/* other fields all nullable */
);
COMMIT;

CREATE GENERATOR GEN_PK_ORGANIZATION;
COMMIT;

SET TERM ^;

CREATE TRIGGER BI_ORGANIZATION FOR ORGANIZATION
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CLIENT_NUMBER IS NULL) THEN
NEW.CLIENT_NUMBER = GEN_ID(GEN_PK_ORGANIZATION, 1);
END^
COMMIT^

SET TERM ;^

Now the following SQL works as expected when executed in IB_SQL:

insert into ORGANIZATION (NAME) values ('Test Organization')

- I get a new row in the table with the generated key.

Now in Delphi I want to access this table using TIBOQuery with RequestLive
= True, connected to a set of TEdit components (legacy code). To save
edits and inserts I use the following code (having previously started a
transaction when calling Insert or Edit on the query):

with iboQuery1 do begin
if State in [dsEdit, dsInsert] then
try
Post;
if IB_Transaction.InTransaction then
IB_Transaction.Commit;
except
if IB_Transaction.InTransaction then
IB_Transaction.Rollback;
end;
end; {with}

When I execute this code after inserting into the query, I get the
following exception at the Post call:

EDatabaseError with message 'Field 'CLIENT_NUMBER' must have a value'.

I have tried setting the InsertSQL property of the TIBOQuery to:

INSERT INTO ORGANIZATION(
NAME,
/* other fields snipped */)
VALUES (
:NAME,
/*other parameters snipped */ )

but I still get the error.

What is the correct way of inserting rows in the table in this
situation? Am I misunderstanding how InsertSQL is supposed to be used?

Thanks,

Walter

/*----------------
C. Walter Ogston
ogstoncw@... Kalamazoo, Michigan
*/