> Isn't this one of the silliest things? Why in the world
> would a default value not work. IMHO, it should not matter
> if your insert statement includes a field that is null. If
> the column is defined w/ a default value, the table should
> use it, otherwise don't define a default value.
> This produces a lot of extra code (before insert triggers)
> just to make sure of values. Ugh.

I agree about the extra code - I have literally thousands of
lines of trigger code specifically to avoid have NULL states
on my table fields.

However the logic is relevant to the SQL client/server
situation. The server has no "insert state", as users are led
to understand in most client interfaces. There is only the
insert statement - and if it says it wants some specific value
(or state) in a field, then why should default be used to
override it? This would have impacts on implementations that
make active use of NULL states (such applications do exist).

But the other side of the argument (as you already know) is
that there are many applications that do not need or want the
complication that NULL states present. I do not deny that
they are useful and even necessary in specific situations, but
where these complications are not necessary I do think there
should be a better way of handling the situation than writing
copious trigger code.

The obvious solution is to declare all fields in which you do
not want NULL, as NOT NULL (and perhaps you do). Of course
the problem then is to ensure that your client (and all other
uses of the field) actually provides appropriate defaults
during insert. So all you have managed to do is move your
code from the centralised server to all instances of client
use of the field. Which is obviously not convenient, or even
desirable, in a client/server implementation.

I think that a neat solution could be for SQL to support a
"FORCED DEFAULT ..." syntax (ie; just extend the existing
syntax). For maximum convenience it would good is such syntax
caused two actions during insert and update...

- before any triggers fire ensure the forced default value
is applied to any applicable NULL fields

- immediately after all the BEFORE insert/update triggers
have been processed (but before storage takes place)
that any applicable NULL fields have their FORCED DEFAULT
re-applied if needed.

The first step is to ensure that triggers can be written as
though the default has been applied, the second step is to
ensure that before triggers do not invalidate the forced
default.

The first step is the important one - since that is the one
that will lead to the biggest reduction in the number of lines
of trigger code (no need to check for null if a field has been
forced).

The second step is perhaps redundant (or even undesireable if
the field has been declared nullable). I am open to argument
on this one, but at the moment I think it may be convenient.


Whether such extension to the standard SQL syntax would be
considered acceptable I do not know. But it would certainly
save writing huge amounts of trigger code when all you want
to do is avoid the complication of NULL where it is not
required.

I would be interested to hear if there were others interested
in such a concept (or perhaps others that think it is may be
a terrible idea). If there was interest then I could put the
idea to developers to see if...
- it was practical
and
- it was desirable.

--
Geoff Worboys
Telesis Computing