--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

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

thank you for the idea Helen