[SOLVED] MySQL after update, update datetime column of same table

MySQL after update, update datetime column of same table

I have a table called teamMembers, this table stores a name and an e-mail address of a person:

id       INT (UNIQUE, AUTOINCREMENT)
name     VARCHAR(255)
email    VARCHAR(255)
updated  DATETIME
created  TIMESTAMP

I've created my trigger using phpMyAdmin and it looks like this in this export:

CREATE TRIGGER teamMembers.updated_updater 
AFTER UPDATE ON teamMembers
    FOR EACH ROW 
        BEGIN
            UPDATE teamMembers SET updated = NOW() WHERE id = OLD.id;
        END

So when I use a query like this: UPDATE teamMembers SET name = 'test' WHERE id = 1 I should set the updated column to the current datetime.

This is not working though, I get this error message:

#1442 - Can't update table 'teamMembers' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I cant really figure out what the problem is here. I think it's because this trigger would invoke itself as well and would therefore become an endless loop. I think there must be a way to auto-update a record that has just been updated without altering my query.

Could someone help me to use a MySQL trigger to update to updated column?

Solution

Use a before trigger:

CREATE TRIGGER teamMembers.updated_updater 
BEFORE UPDATE ON teamMembers
    FOR EACH ROW 
BEGIN
    SET new.updated = NOW();
END