MySQL: Solution for ERROR 1442 (HY000): Can’t update table ‘t1′ 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 `t1` (
`a` char(1) default NULL,
`b` smallint(6) default NULL
);
insert into t1 values ('y','1');
I have a table t1 which has column a and b, I want column a to be updated to ‘n’ when column b = 0. Here is the first version I created:
DELIMITER |
CREATE TRIGGER trigger1 AFTER UPDATE ON t1
FOR EACH ROW UPDATE t1 SET a= 'n' WHERE b=0;
|
DELIMITER ;
The trigger created successfully but I got this error when I tried to do an update on column b on table t1:
mysql> update t1 set b=0;
ERROR 1442 (HY000): Can't update table 't1' 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:
drop trigger trigger1;
DELIMITER |
CREATE TRIGGER trigger1 BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
IF NEW.b=0 THEN
SET NEW.a = 'n';
END IF;
END
|
DELIMITER ;
After the new trigger is in, I issued the same update query and “ERROR 1442 (HY000): Can’t update table ‘t1′ 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 a value to “n” as it suppose to.
mysql> update t1 set b=0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1\G
*************************** 1. row ***************************
a: n
b: 0
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!
However, if you are updating some other table, then you can use the regular update statement:
DELIMITER |
CREATE TRIGGER trigger1 AFTER UPDATE ON t1
FOR EACH ROW UPDATE t2 SET a= ‘n’ WHERE b=0;
|
DELIMITER ;
————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.
(9 votes, average: 2.78 out of 5)
Loading ...
I actually had to read through this twice, because I was working on something similar at the time. If you’re trying to do an update-join, and one of the tables has a trigger that updates the other table in the join, it will fail.
For example, if you have these tables:
picture (pictureid, name, num_comments)
comment (commentid, pictureid, userid, comment, num_ratings),
rating (commentid, userid, rating)
Pictures have comments, comments have ratings.
If you’re trying to copy a foreign key from the comments to the ratings (say the pictureid, for performance reasons), and you have a trigger on ratings to update an aggregate in comments (in this case, the rating count), this query will fail:
update comment c JOIN rating r on c.commentid = r.commentid
set r.pictureid = c.pictureid;
You could get around it by only having the trigger fire if the rating changes.
Thanks for the great post. It helped me solve my problem with setting up my first trigger.
Thanks Tracy. This drop/create trigger gambit appears to be standard methodology. BUT, I am trying out Navicat and this seems to stand in the way of normal MySQL execution/saving/defining of MySQL objects.
Help with table, thanx up front!
DROP TABLE IF EXISTS `donations`;
CREATE TABLE `donations` (
`id` int(11) NOT NULL auto_increment,
`itemname` varchar(255) NOT NULL default ”,
`gross_price` varchar(10) NOT NULL default ”,
`pay_email` varchar(255) NOT NULL default ”,
`paymentdate` date NOT NULL default ‘0000-00-00′,
`firstname` varchar(100) default NULL,
`lastname` varchar(100) default NULL,
`memo` varchar(255) NOT NULL default ”,
`paymentstatus` varchar(20) default NULL,
`pendingreason` varchar(20) NOT NULL default ”,
`txnid` varchar(30) NOT NULL default ”,
`anom` char(3) NOT NULL default ‘no’,
`stdate` date default NULL,
KEY `paymentstatus` (`paymentstatus`),
KEY `stdate` (`stdate`),
KEY `txnid` (`txnid`)
) TYPE=MyISAM AUTO_INCREMENT=1;
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key.
Cane, please use forums for questions not related to posts. I posted a correct table definition at: http://crazytoon.com/forum/viewtopic.php?f=3&t=6