Milan Babuskov wrote:

>
>> 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 you want to insert NULL, your column will be nullable and maybe with a
default.
If your column is NOT NULL and has a default (the case Geoff was exposing),
then you don't have a way to insert NULL and therefore, no harm has been
done. You only have saved time by not having to retrieve the default from
somewhere to replace the incoming NULL.

>> 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.

Don't see the problem. Again, it seems you didn't get the point the Geoff
only worries about
DEFAULT <v> NOT NULL
cases.

>> 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.

So I can connect to your db with any program that's not your specific
application and screw everything with a simple update?
:-)

C.