Mysql Error Number 1442
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack can't update table in stored function/trigger because it is already used by statement Overflow the company Business Learn more about hiring developers or posting ads with us
Mysql Trigger Update Same Table
Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a
Updating Of New Row Is Not Allowed In After Trigger
community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up what is the real cause of mysql error 1442? up vote 5 down vote favorite well
Create Trigger Mysql
i have looked for a lot of places on the internet for the cause of the mysql error #1442 which says Can't update table 'unlucky_table' in stored function/trigger because it is already used by statement which invoked this stored function/trigger some say that this is a bug in mysql or a feature that it doesnt provide. MySQL triggers can't manipulate the table they are assigned to. All other major DBMS support this feature so hopefully MySQL will add this support soon. Some claim that this is due to recursive behavior when you insert a record mysql is doing some lock stuff. you can't insert/update/delete rows of the same table where you insert.. because then the trigger would called again and again.. ending up in a recursion During the insert/update you have access to the NEW object which contains all of the fields in the table involved. If you do a before insert/update and edit the field(s) that you want to change in the new object it will become a part of the calling statement and not be executed as a separately (eliminating the recursion) now i cant understand why this is recursive. i have a case in which i have 2 tables table1 and table2 and i run an sql query as update table1 set avail = 0 where id in (select id from table2 where duration < now() - interval 2 hour); now i have an after update trigger on table1 as CREATE TRIGGER trig_table1 AFTER UPDATE ON table1 FOR EACH ROW begin if old.avail=1 and new.avail=0 then delete from table2 where id=new.id; end if; now when i execute the update query i get a 1442 error. whats recursive in t
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join http://stackoverflow.com/questions/6756454/what-is-the-real-cause-of-mysql-error-1442 them; it only takes a minute: Sign up Error #1442 MySQL - making a trigger up vote 0 down vote favorite I have a problem to making a trigger. I want make a 'copy' of field 'nome' in 'nome1' whenever I make an insert of a record (casello). I receive this error: #1442 - Can't update table 'casello' in http://stackoverflow.com/questions/17295051/error-1442-mysql-making-a-trigger stored function/trigger because it is already used by statement which invoked this stored function/trigger. How Can i fix it? this is the structure of my table: -- -- Struttura della tabella `casello` -- CREATE TABLE IF NOT EXISTS `casello` ( `id` int(3) NOT NULL AUTO_INCREMENT, `posizione` double NOT NULL, `nome` varchar(100) NOT NULL, `nome1` varchar(100) DEFAULT NULL, `modalita_pagamento` varchar(255) NOT NULL, `servizio_assistenza` tinyint(1) DEFAULT NULL, `l_nome` varchar(100) DEFAULT NULL, `nome_autostrada` varchar(4) NOT NULL, PRIMARY KEY (`id`), KEY `l_nome` (`l_nome`), KEY `nome_autostrada` (`nome_autostrada`), KEY `nome` (`nome`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ; -- -- Dump dei dati per la tabella `casello` -- INSERT INTO `casello` (`id`, `posizione`, `nome`, `nome1`, `modalita_pagamento`, `servizio_assistenza`, `l_nome`, `nome_autostrada`) VALUES (3, 35, 'napoli nord', NULL, 'contanti, carte, telepass', 1, 'caserta', 'a1'), (4, 15, 'napoli sud', NULL, 'contanti, carte, telepass', 1, 'napoli', 'a1'), (5, 310, 'roma nord', NULL, 'contanti, carte, telepass', 1, NULL, 'a1'), (6, 280, 'roma sud', NULL, 'contanti, carte, telepass', 1, NULL, 'a1'), (11, 25, 'palma campania', 'NULL', 'contanti, carte, telepass', 1, NULL, 'a30'), (12, 30, 'sarno', 'NULL', 'contanti, carte, telepass', NU
Community Podcasts MySQL.com Downloads Documentation Section Menu: MySQL Forums :: Triggers :: ERROR 1442 - Cannot update table which has trigger New Topic Advanced Search ERROR 1442 - Cannot update table http://forums.mysql.com/read.php?99,573445,573445 which has trigger Posted by: Daniel Xyz () Date: November 10, 2012 05:21AM 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/ The problem is like in a title, Ive read some solutions and used "NEW.colum_name" keyword to solve the problem. Trigger definition: CREATE TRIGGER `trigger1` BEFORE UPDATE ON `driver` FOR EACH ROW BEGIN UPDATE driver SET NEW.is_available=0 WHERE NEW.break_finish_time >= now() && NEW.break_start_time <= now(); END And when I want to update mysql error table 'driver': ERROR 1442: Can't update table 'driver' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. SQL Statement: UPDATE `taxi`.`driver` SET `break_finish_time`='2012-11-09 12:13:00' WHERE `iddriver`='2' 'driver' create statement: ================================================================ delimiter $$ CREATE DEFINER=`root`@`localhost` TRIGGER `taxi`.`trigger1` BEFORE UPDATE ON `taxi`.`driver` FOR EACH ROW BEGIN UPDATE driver SET NEW.is_available=0 WHERE NEW.break_finish_time >= now() && NEW.break_start_time <= now(); END $$ mysql error number CREATE TABLE `driver` ( `iddriver` int(10) unsigned NOT NULL AUTO_INCREMENT, `is_available` tinyint(1) NOT NULL, `driver_name` varchar(45) COLLATE utf8_polish_ci NOT NULL, `driver_surname` varchar(45) COLLATE utf8_polish_ci NOT NULL, `driverpoint_horizontal` double NOT NULL, `driverpoint_vertical` double NOT NULL, `break_start_time` timestamp NULL DEFAULT NULL, `break_finish_time` timestamp NULL DEFAULT NULL, PRIMARY KEY (`iddriver`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci$$ ================================================================ It doesnt have any foreign keys, however other tables does have foreign key which uses 'iddriver' from 'driver' table. Is there any solution? Edited 1 time(s). Last edit at 11/10/2012 05:22AM by Daniel Xyz. Navigate:Previous Message•Next Message Options:Reply•Quote Subject Views Written By Posted ERROR 1442 - Cannot update table which has trigger 8076 Daniel Xyz 11/10/2012 05:21AM Re: ERROR 1442 - Cannot update table which has trigger 3453 Devart Team 11/13/2012 01:30AM Re: ERROR 1442 - Cannot update table which has trigger 2214 Daniel Xyz 11/18/2012 07:26AM Sorry, you can't reply to this topic. It has been closed. powered by phorum Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party. C
it is already used by statement which invoked this stored function/trigger. 40 Replies 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 yo