Re: Trigger for modification timestamp column

On Wed, Jul 7, 2010 at 8:48 AM, Johan Andersson <warb(at)mail(dot)com> wrote:

>
> Hello!
>
> I am trying to write a trigger for updating a modification column and am
> having some trouble getting it to behave as I want.
>
> The trigger should set the column to the supplied value if it is set in the
> UPDATE statement and to the current timestamp [NOW()] if it is not. The
> problem is that I don't know how to check if the column is set or not. I
> can
> check the column's value for NULL but that doesn't work if I want the
> column
> to accept NULL values (meaning "unmodified").
>

It isn't very clear just what modification it is you're tracking, a specific
column or the entire row?

Is this a column of type timestamp?

You can do a comparison between OLD.XXX and NEW.XXX in a before-update
trigger but you will need to take into account null values. For example, is
it possible for the modified flag go from non-null to null, and if so what
does that mean?

Suppose the update statement explicitly sets XXX to the same value it
already has, is that updating it or not?