At 07:00 AM 12/09/2007, Robert Martin wrote:

>The SvrRecCnt field is NULL for these entries. The records
>inserted above all have a SvrRecCnt field with a numeric value. I fail
>to understand why you say that the where clause could select records it
>has inserted.
>
>I know it is not elegant logic but it should work. SvrRecCnt if a
>foreign key into two other tables.

Then it isn't being used for what I assumed.

>What is the Fatal flaw?

That your WHERE clause selects records by a field value in order to
insert records containing the same value for that field. Under the
standard, selection should stop once the working set has read all of
the existing records. In Fb and IB, it keeps on selecting the
records it has just inserted, if they match.

> If you explain the flay I could work around it.

I thought it was obvious, from the solution I suggested and
discussed. Oh well..

Your original description made it appear that your WHERE clause was
looking for records with SvrRecCount null and was initially inserting
them with null, only later going in to update SvrRecCount.

To cure the immediate problem, eliminate the PK from the INSERT
statement and have the trigger get the generator value, rather than
risk having your statement fetch more than one record for copying and
attempt to write the same PK value when the statement hits subsequent records.

Helen