At 08:36 PM 23/06/2009, you wrote:

>thank you for your reply Hellen, but I still don't understand some things.
>
>>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.
>
>
>Firebird reacts the same if I write
>
>insert into table (field1, field2,field3,...)
>values(value1,value2, value3,...)
>
>where for example value2 is null
>
>or
>
>insert into table (field1, field2,field3,...)
>
>values(value1,null, value3,...)
>
>or
>
>insert into table (field1, field3,...)
>
>values(value1, value3,...)
>
>generating the "field <field> value is null" for the field with the default source, (field2 in the example).
>
>I know that when I "insert" into the table or the updatable view w/o BI trigger using some client software (e.g. IBExpert, FlameRobbin etc) it is not clear what the software does behind the scenes, but in this the case when I don't get the error message and the default value works. In all other cases it does not.

Yeah, it looks confused.

Watch out for program interface layers that pass "zero-value" in fields that they interpret as "empty". Programming languages don't have "null" so, typically, they will pre-process your statement and provide an empty string for character fields and zero or zero-equivalent for others. When you inspect the rows afterwards with your GUI tool, you are fooled by what you see. Quite a few of the Delphi components out there behave this way, thanks to the heritage from the Paradox engine (BDE). It's right for Paradox; it's wrong for Firebird. It's wrong for Firebird *even if* the Delphi defaults are the same as the ones you want, because it pushes your data integrity out of reach of the database engine.

You can write a test trigger to test the exact behaviour, if you like. Have your test trigger test if new.field2 is null and, if so, apply a value that is *different* to the one you defined as the default value.

-- If the insert statement did not include field2 then you should see the defined default value written

-- If the insert statement *did* include field2 and passed NULL in lieu of a value, then the trigger should do its work and you will see that new.field2 gets the trigger's value

That way, you can figure out what is happening under the surface and plan accordingly.

./heLen