>> > create un_table (c1 int unique)
>> > <insert rows>
>> > update un_table set c1 = c1 + 1
>> > is valid.
>>
>>Valid maybe, but it only works in certain situations, not
>>reliably.

> According to the standard, the constraint is consistent with
> the update and should work reliably. Firebird checks its
> constraints before the "operation". The standard says that
> immediate constraints are checked after the "verb". The
> verb is the whole of the update, not the change to individual
> records.

So the fact that it does not work reliably in Firebird is an
artifact of FBs non-compliance with the standard.

It looks to me like "after the verb" could get ugly. While I
can see the apparent logic of why such an update _could_ be
expected to work, the potential costs of involved in supporting
this seem very high. And not just the performance and locking
that I mentioned in a previous post...

Consider triggers. If constraint testing moved to after the
verb then after-action triggers would also have to fire after
the verb (after the constraint testing), or they would be
working with potentially inconsistent data.

And we get more problems because any cross-row logic in a
before-action trigger may be looking at inconsistent data.
If all the before-action triggers fire before the verb then
such cross-row logic is looking at old data. If the before-
action triggers fire as each row change is applied then any
other referenced rows may still contain inconsistent data (not
yet verified by declared or trigger constraint).

I have not worked out whether various logic sequences involved
in this could lead to loss of data integrity - although I do
expect it is possible. But, if nothing else, you could end
up with great confusion. eg: A before-action trigger could
report "multiple rows in singleton select" - not because this
is the problem, but because it is looking at inconsistent
data where a unique constraint has not yet been verified.

Then add to this the various situations where trigger actions
cascade changes to other tables and the potential for trouble
grows even greater.

A change to "after the verb" changes the premise on which much
existing logic is based - that any data that actually gets into
a table is consistent with its integrity constraints (declared
or trigger imposed). Such a change would mean redesigning and
retesting all but the most simple of existing applications.

Like I said, the costs are high. I dont want to pay these
costs for the privilege of a minor point of standard compliance.

--
Geoff Worboys
Telesis Computing