[RESOLVED] Recording the date a particular field is updated

[turtleton09]

Hope someone can help with this.

At the moment I have a table, which has LastUpdated field that records when a record is updated.

This is mainly being used to enable the site owner to send out email shots like so :

1. An email address is added to a record, and records the date.

2. At the end of the day, a search is carried out for that date.

3. A list of contacts updated (had an email address added) that day is returned.

4. They click through to a compose email page, which in the background updated an EmailList field fron N to Y.

5. The email is sent to contacts where EmailList=Y.

6. A button at the end resets the EmailList field back to N for the next time its used.

Which all works great, but we've had a couple of issues where users haven't quite appreciated what is happening, and have managed to return all contacts in error, update the EmailList for them all to Y, then abort the process midway, without completing the step which resets that field back back to N.

So... what I think would resolve this is to have an EmailAddressUpdated field which records when the EmailAddress field is updated, rather than the existing general LastUpdated field which records when the record is updated in any way.

Is this possible?

Cheers.

[turtleton09]

From a rummage, I've figured out I should be able to do it in phpMyAdmin with something like :

CREATE TRIGGER fieldchanged BEFORE UPDATE ON mytable
FOR EACH ROW
BEGIN
    IF OLD.FieldName <> NEW.FieldName THEN
        SET LastUpdate = NOW();
    END IF;
END;

But that gave me the error :

Unknown system variable 'LastUpdate'.

Is that barking up the right tree?

[Weedpacket]

SET NEW.LastUpdate = NOW();

[turtleton09]

Thanks for that - hit a brick wall as Super Privileges are required to set Triggers.

Have been on to the hosts, and apparently they're testing mySQL 5.1 which has new Trigger Privileges.