Subject RE: [Firebird-Architect] NOT UPDATABLE fields
Author Claudio Valderrama C.
Dmitry Yemanov wrote:
> Personally, I'm with Sean. This reminds me a recent discussion in one
> Russian forum regarding update defaults existing in Sybase ASA.
> The idea is
> to have an alternative default clause which is used in update
> statements, i.e. when a column is not mentioned in the SET clause and
> an update default
> expression is defined for this column, it's being applied
> automagically. Is
> it a useful feature? Perhaps, for some logging scenarios. But my
> opinion was
> that our triggers is exactly what's required for this task. The
> opponent argued that a trigger-based solution is too slow and
> confirmed that fact with performance numbers. But the truth was that
> the trigger invocation in ASA is very expensive (no trigger vs empty
> trigger showed almost 100% performance difference) whilst ours are
> quite fast (10% penalty).
> After that
> the only remaining question was the syntax - whether a DDL
> declaration is better than a one-line trigger. I'm still not
> convienced it is. I believe that Adriano's proposal is very similar
> to that story, hence I don't think such a feature is really
> important. But I don't say a strong "no" either. Perhaps, we'll need
> a voting ;-)

The suggestion for UPDATES seems dangerous. Suppose a GUI and some
sophisticated connectivity layer (examples abound). From each record shown,
the user changes one or two fields, then the library can have a prepared
UPDATE statement with all "modifiable" fields, I mean discarding computed
fields and dbkeys. For unchanged fields, it could submit the old
information. Otherwise, the only option is for the library to forget about
prepared updates and do a custom update statement for only the fields it
detected as changed. It trades an unprepared statement for less network
traffic. If the server gets in the middle saying that unchanged fields
should not be preserved but instead reverted to some default, obviously I
don't want this happening automagically. I would demand the application to
send some keyword to indicate that behavior... and the server would have to
recognize it.

However, I consider the cae for INSERTS more acceptable. Let's see, everyone
knows that a long list of
literal statements will be much slower than preparing the statement once,
and go in a loop feeding the parameters and executing the insertion. In this
scenario, defaults have a clear meaning: fields that weren't specified in
the insert list get their default value. However, it requires to build a
custom INSERT for every input row, thus losing the efficiency of a prepared
statement. Then an alternative may be to prepare the full insertion and go
feeding values. For absent values, instead of throwing away the prepared
statement, those values are replaced by a keyword telling the server to
apply the default as if such field wasn't part of the insertion. Seems
acceptable to me and I'm not sure why there seems to be strong opposition to

Comparing those two scenarios (defaults in UPDATES and defaults in
INSERTIONS), Adriano's proposal falls in the same category than defaults for
UPDATES for me: it's not clear who will take advantage of such feature and
also, an update default really becomes a "revert to this value if not
specified" because the field already had data.