Geoff Worboys wrote:

> Performance and efficiency:
>
> Many interfaces prepare a full insert statement regardless
> of whether values are entered into a particular field by the
> user. Thus NULL will often get submitted by the application
> and prevent normal DEFAULT values from being applied by the
> server (and so NOT NULL on the fields result in exceptions).

I believe this is a responsibility of these "many interfaces". However
created them, and said they support Interbase should have known better.
It is also a responsibility of application developers to use their
interfaces. BTW, could you name some of the "many", I know only one
(Delphi), and workaround is much simpler then generating triggers.

> This can be avoided by preparing a new insert statement for
> each insert, so the insert statement can be created to only
> include fields with values supplied. As you can imagine this
> is far from efficient if many inserts are being performed.

Good point.

> Having a default that can apply to null columns, whether the
> NULL was specified on the insert or the result of the column
> not being specified, allows inserts to be more efficient.

What if I really want to insert NULL in column that has a default value?

> If the enforced assignment of default values was to occur
> before any insert or update triggers are fired it becomes
> possible to write trigger code that can assume valid input
> values - allowing the code to be much clearer and more
> succinct.

And make a lot of trouble with backward compatibility of both databases
and applications.

> In a great many applications the distinction between NULL
> versus "empty" (0 number or blank string) is not relevant.

I disagree. I diffrence is huge.

> Indeed in most of my work the users really do not want to
> know about "missing" values, it is not of any interest. If
> they dont supply the value they generally expect it to be
> empty (not missing/null/unknown).

Why not do it in your application? Set the fields initially to 0 or ''
or whatever.

> If I can create my tables/columns such that enforced defaults
> are there in the declaration, I have not only reduced the work
> of writing triggers, I have also declared the intentions of my
> design in clear and easy to read form (rather than browsing
> huge amounts of trigger code). And this has got to be a good
> thing.

Well, I guess it's just your POV. In all my applications I don't have a
single BEFORE/AFTER UPDATE trigger. I do have many on insert and delete
operations, but none of them addresses this issue.

If a field can be empty, just like you wrote the client doesn't care if
it is null, 0, or ''. So why bother, just declare field nullable, and
leave it components you use to insert nulls.

Just my $0.02 (ok, maybe a little more).

Regards,

--
Milan Babuskov
http://fbexport.sourceforge.net
http://www.flamerobin.org