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