At 06:57 PM 4/01/2005 +0000, you wrote:

>Hi,
>
>On a Firebird DB, I set up a table with a "before insert" trigger.
>When a new records is added to the table, the trigger automatically
>adds a record in another table.
>
>This is a very common scenario for activity logging, although I use it
>for some other purposes as well.
>
>I tested this setup with direct SQL commands, and it works as it
>should. However when I try to insert through a Delphi form with IBO
>query, I get an exception that reads "multiple records inserted".

On the client side, IBO has no knowledge of triggers or their effects.

>I tried everything I could think of, and I searched everywhere, but
>could not find a mention of such a problem. Then I searched through
>IB_Constants.DCU and found the exception string in there. This hints
>that IBO throws the exception, not Firebird.

Correct. IBO reads the RowsAffected count from the structure that is
returned from the execution of the DML statement. It uses this number to
determine whether the Insert statement it passed across the wire had the
logically proper effect of inserting one and only one row to the dataset's
underlying table.

> I suspect that IBO checks how many rows were affected after the
> insert, and it only allows for a single row - anything else and IBO
> throws an exception.

For the server's part, it performs the single insert. If it were to stop
there and return the rows affected count to the client, it would be 1,
which IBO's reality check would accept. With your trigger, in turn, the
server proceeds to insert some rows into another table. As it does each of
these "secondary" inserts, it increments the count of rows affected. Thus,
when IBO finally gets the count, it is more than one, and IBO delivers a
warning about it - the message you saw.

>Is there any way to tell IBO to allow more than 1 row per insert?
>Maybe another workaround?

No, but no workaround is necessary! As the programmer, you know that this
particular Insert call is going to cause this condition when the Insert
operation executes the DML --- and you know that it's OK. To you, this
particular exception is merely an alert that confirms that your Insert
operation had the desired effect. So just handle that particular exception
and swallow it. (If you don't, of course IBO has no way get past the
exception condition other than to raise it and stop the show!!)

>Note that using a stored procedure isn't a good solution, because it
>means I have to write and maintain dozens of SPs, which is a nightmare.

Quite right. That would be a "workaround" for a problem that doesn't
exist. :-)

Helen