Devart Forums

Posted: **Wed 10 Oct 2007 23:29**

Hi,
having a FB table with a before insert trigger

CREATE TABLE TABLEA (ID   BIGINT NOT NULL,
    NUM  INTEGER NOT NULL);
CREATE TRIGGER TABLEA_BI FOR TABLEA
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_tablea_id,1);
end

when inserting a new record and calling Post, the EDatabaseError occurs with message 'Field ID must have a value'. Actually I am using only TIBCQuery, TIBCDataSource, TDbGrid and TDbNavigator for calling Insert, entering data for the Num column only and calling Post.

But doing the same by the use of TIBCSQL with a statement

Code: Select all

INSERT INTO tablea (Num) VALUES (:Num) RETURNING ID;

inserts a new row without any error. Again, only the value for the NUM field is supplied.

I would like both - to use the before insert trigger for filling the primary key field ID and to call the TIBCQuery.Post method for posting the data. According to IBDAC help it should work. Please, any hint where am I making a mistake ?

Posted: **Thu 11 Oct 2007 12:44**

You can avoid this error in two ways:
1. Create fields for the TIBCQuery component in the Fields editor and set the Required property for ID to False.
2. Set the RequiredFieds option of the TIBCQuery component to False.

After calling the Post method, you should call the Refresh method of TIBCQuery to get a value of ID field generated by the trigger.

In the next IBDAC build we'll also correct the behaviour of the DMLRefresh property of TIBCQuery so that you will be able to use this property instead of calling Refresh. When this property is set to True, the Post method of TIBCQuery uses INSERT statement with a RETURNING clause to insert a record to the database.
In current version of IBDAC the DMLRefresh property cannot be used for your situation, because ID field is included in the RETURNING clause only if you assign its value in your program before post.

Devart Forums

Post and Before Insert Trigger

Post and Before Insert Trigger