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.
(12 votes, average: 2.42 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
Hi friend,
I am facing problem in creating database. Would you please help me.
Hello!
I need a catalog tree table. How can I create trigger to check a column with “level of subcatalog”?
When some catalog moved into another all downlaying catalog must recount theirs level.
This is possible?
Unfortunately this is NOT a solution to the problem. It only works if the record you want to update is the same record triggering the trigger.
What if I want to update a DIFFERENT row in the same table? For example, I’ve a tree stored in the table and want to update the parent_id’s in all affected rows when a row (node in the tree) is deleted. Impossible! What’s more, there is no reason for this mysql behavior. I use INNODB, which should have row-level locks. I’d understand the error in MYISAM tables which use table-locking.
Fact remains, it’s a really stupid mySQL bug. Oh well, one more reason to use a REAL database like Oracle
i need a solution to update DIFFERENT row in same table too, who can help??
Michael, TMD: same problem here!
what about updating different row in the same triggered table?
php myadmin error
I have a same problem. I create a trigger AFTER INSERT on tabla1 , in it i want to UPADTE the same tabla1 . But when i insert a row, i get the next error:
Error Code : 1442
Can’t update table ‘cuentacontable’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
(32 ms taken)
Please. Help me, Thanks.
I got a msg when creating a view.
Error Code : 1142
CREATE VIEW command denied to user ‘root’@'user.com’ for table ‘tbl_ABC’
thanks in Advance..