Thank you all,

Ivan's solution is exactly what I was looking for...

Marcin


W dniu 28.02.2010 11:58, Helen Borrie pisze:

> At 08:56 PM 28/02/2010, you wrote:
>> Helen and others
>>
>> I have a table where I have a field COMPLETED_DATE of 'date' type.
>> When I insert a record to this table I leave this field null.
>>
>> Then I'd like to have some 'after update' trigger that would fire when
>> COMPLETED date has changed, so I have tried:
>>
>> IF (COALESCE(OLD.COMPLETED_DATE,0)<> COALESCE(NEW.COMPLETED_DATE, 0))
>> THEN ...
>>
>> I thought that it should work since 'data' type is some kind of
>> 'numeric' type. But I was wrong - and got error as in subject.
>>
>> Following Helen's suggestion I've tried:
>> COALESCE(OLD.COMPLETED_DATE, CAST(0 AS DATE))
>> but with the same result...
>
> I have no idea how you figured that I suggested that, from the hint I gave you. 0 is not a date and it cannot be cast as one. I said you could compare a date with a date! One of my examples showed how you could cast a date literal as a date.
>
>> I know I'm doing something wrong, but to avoid further 'dark' questions,
>> I'd like to know what it is.
>
> Ivan has given you a workable answer, if you are using Fb 2 or higher.
>
> IF (COALESCE(OLD.COMPLETED_DATE,0)<> COALESCE(NEW.COMPLETED_DATE, 0))
> THEN ...
>
> And the coalesce expression simply can't be used to test this, since the "null replacement" value can only be a date, viz., CURRENT_DATE or a date literal (such as 'today' or '2009-12-25') cast to its type.
>
> Sans the IS DISTINCT FROM predicate (Fb 1/1.5), your update trigger has to test all the possibilities to determine whether the date changed:
>
> IF (OLD.COMPLETED_DATE<> NEW.COMPLETED_DATE
> OR (OLD.COMPLETED_DATE IS NULL AND NEW.COMPLETED_DATE IS NOT NULL)
> OR (OLD.COMPLETED_DATE IS NOT NULL AND NEW.COMPLETED_DATE IS NULL))
> THEN...
>
> ./heLen