My solution to:MySQL triggers cannot update the same row that the trigger is assigned to.

My solution to: Error,Can't update table 'XXX' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Here is a sample table you can create to test following problem/solution on:

create table if exists userInfo;
create table `userInfo` (  
`uid` int(11) NOT NULL,  
`name` int(11) NOT NULL,
`updateTime` datetime NOT NULL,
PRIMARY KEY  (`uid`)  
) ENGINE=innoDB  DEFAULT CHARSET=utf8 ;


create trigger if exists t_updatetime;
delimiter //
create trigger t_updatetime after update on userInfo
for each row
if new.name != old.name then
    update userInfo set updateTime=now() where uid=old.uid;
end if;
end //
delimiter ;

The trigger created successfully but I got this error when I tried to do an update on column name on table userInfo:
mysql> update userInfo set name='xxx' where uid = 1122;
ERROR 1442 (HY000): Can't update table 'userInfo ' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.


After searching online for a while and trying different solutions, I finally found a way to update the table which has trigger on it:

create trigger if exists t_updatetime;
delimiter //
create trigger t_updatetime before update on userInfo
for each row
if new.name != old.name then
    set updateTime=now();
end if;
end //
delimiter ;

After the new trigger is in, I issued the same update query and “ERROR 1442 (HY000): Can’t update table ‘userInfo′ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.” didn’t show up and it updated the col updateTime value to 'now()' as it suppose to.

Therefore, if you want to create a trigger on the table which will update itself, make sure you use the NEW.column_name to refer to the row after it’s updated and don’t do the full update statement!

类似问题链接：

http://stackoverflow.com/questions/2334478/mysql-triggers-cannot-update-rows-in-same-table-the-trigger-is-assigned-to-sugg

http://crazytoon.com/2008/03/03/mysql-error-1442-hy000-cant-update-table-t1-in-stored-functiontrigger-because-it-is-already-used-by-statement-which-invoked-this-stored-functiontrigger/

http://www.themysql.com/mysql/%E5%85%B3%E4%BA%8Emysql-insert%E8%A7%A6%E5%8F%91%E5%99%A8cant-update-table-tbl%E9%94%99%E8%AF%AF.html