On Wed, Jul 7, 2010 at 8:48 AM, Johan Andersson
<warb@mail.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?