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

超、凢脫俗 2022-06-17 07:54 151阅读 0赞

**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