At 01:25 AM 21/06/2009, you wrote:

>Hi all,
>
>I am currently using Firebird-2.5.0.24118-0, but I have seen the same behavior in earlier versions as well.

That's because it is designed that way. ;-)

>I have a table with a field using the default source property to get it's value when null is inserted in the table and an updatable view on the table.
>
>When I insert a record directly to the table, the default source of the field works as expected, the same when I am using the view to insert the record _IF_ the view is "directly" updatable, meaning that it uses only the given table w/o any before insert triggers to update the underlying table.
>
>If before insert triggers are used, such as
>
>insert into table(field...)
>values (new.view_field,...)
>
>I get an "field <field> value is null" for the field with the default source. I certainly can bypass the problem using something like coalesce(new.view_field,<default value>), but I would like to know the reason of this behavior.

NULL is not a value: it is a state. When *updating* a record, we can pass an explicit SET AFIELD=NULL in our UPDATE statement when we want to change the state of a nullable field to NULL. In an INSERT statement, although it is possible to include the nullable field in the column list and pass NULL to it explicitly, we don't need to. In Firebird and other standard-compliant engines: NULL is the default state of all fields.

Defining a default value has exactly one purpose: in an INSERT statement, it overrides the standard behaviour and ensures that a specific value is written into a field that is not specified in the INSERT statement. It is not intended to replace an explicitly passed NULL and it won't work if you try to use it that way.

>any suggestions highly appreciated.

If you want the default value to be used in the INSERT statement, don't include that field in the column list.

If you're using a data access layer that implements an Insert operation on a dataset by auto-creating an INSERT statement that uses all of the fields in the dataset's SELECT statement (such as Delphi, for example) then either :

-- regard that as a problem to handle in your client code (custom InsertSQL and/or providing support for constrained fields)

or

-- address it in a Before Insert trigger.

./heLen