--- In firebird-support@yahoogroups.com, "m.vanzeist"
<m.vanzeist@h...> wrote:

> Hi All,
>
> I am having difficulty distinguishing between a value that has been

inserted

> in contrast to a default value.
>
> Let me clarify.
>
> I use 1 domain
> Domain:MYDOMAIN
> integer
> default 0
>
>
> I have a simple table containing 4 columns
>
> Table:MYTABLE
> 1 ID Integer
> 2 Field1 Integer
> 3 Field2 MYDOMAIN
> 4 Field3 VARCHAR(50)
>
> my before insert trigger is like
> AS
> DECLARE VARIABLE V_DEF1 INTEGER;
> DECLARE VARIABLE V_DEF2 VARCHAR(50);
> BEGIN
> if (new."ID" is null) then new."ID" = gen_id(NEWID,1);
> if ((new.Field1 IS NOT NULL) then begin
> SELECT DEFVALUE1,DEFVALUE2 FROM DEFAULTVALUES WHERE ID =

NEW.FIELD1

> INTO :V_DEF1,:V_DEF2;
> if NEW.FIELD2 is null then
> NEW.FIELD2 =:V_DEF1
> if NEW.FIELD3 is null then
> NEW.FIELD3 =:V_DEF2
> end
> END
>
> in the before insert trigger i want to set up some default values

(Which are

> copied from another table and using the integer for Field1 as an

index), but

> i want to set these default values only when the INSERT statement

has not

> inserted values for specific fields (Lets take Field2 and Field3

for this

> example)
> Field ID is always populated with a generator.
>
> My problem is that i cannot detect if Field2 has been inserted

because it

> will not be NULL due to the fact that the domain has a default
> I could get around this by not providing a default ofcourse and set

the

> default value myself in the trigger, or checking if the value for

Field2 <>0

> (Which i don't want becaus a user could legitimatly insert a 0 here

and then

> the default value must NOT be set)
>
> but is there another way of detecting if a field has been explicitly
> inserted eg INSERT Field1,Field2 values(1,3) instead of INSERT

Field1

> values(1)
>
> Best regards, mario van Zeist.
>

Mario,

You can't do it both ways. If you create a default value as a
constant 0, then the field will have the value 0 when you run an
insert statement without referencing that field.

You have not provided a single example in your problem where you want
it to default to 0?

It seems to me that you should just declare it as an integer field,
and then handle within your trigger setting the default value. Like
this:

if (new.field2 IS NULL) then
begin
if (:VDEF1 IS NULL) then
begin
NEW.FIELD2 =:V_DEF1;
end
else
begin
NEW.FIELD2 =0;
end
end

Adam