Hello,

> > No, always use "old" and "new" ...
> >
> > What I was meaning to say was that an assignment
> > (new.column = newvalue) can only be done in a
> > BEFORE trigger.
>
> OK - Now I'm confused.
>
> I have a trigger defined as BEFORE UPDATE which does this:
>
>
> if (Old.SomeDate<>New.SomeDate) then
> New.MyDateField = New.SomeDate
>
>
> Sometimes it fires. Sometimes not.

I bet it fires every time :-)

That given, can OLD.SomeDate be NULL? If so, you need to do:

if ( (Old.SomeDate <> New.SomeDate) or
(Old.SomeDate is null and New.SomeDate is not null) or
(Old.SomeDate is not null and New.SomeDate is null) )
then New.MyDateField = New.SomeDate;

Of course, edit to your liking.

> If I change the trigger to a AFTER UPDATE trigger it works every time.

Assignments to NEW or OLD should be prohibited in AFTER triggers.

And Firebird 2.0 does this.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com