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