Mysql Error 1442
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 can't update table in stored function/trigger because it is already used by statement or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x mysql trigger update same table Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it updating of new row is not allowed in after trigger only takes a minute: Sign up what is the real cause of mysql error 1442? up vote 5 down vote favorite well i have looked for a lot of places on the internet for the cause of the mysql error create trigger mysql #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 this case? is this error a lack of feature in mysql? OR does this have to do with how mysql executes queries? OR is there something logically wrong with executing such queries? mysql triggers mysql-error-1442 share|improve this question asked Jul 20 '11 at 2:48 lovesh 2,19023575 add a comment| 1 Answer 1 active oldest votes u
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 which has trigger Posted by: Daniel Xyz () Date: November 10, 2012 05:21AM 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` http://stackoverflow.com/questions/6756454/what-is-the-real-cause-of-mysql-error-1442 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 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 http://forums.mysql.com/read.php?99,573445,573445 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 $$ 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 8074 Daniel Xyz 11/10/2012 05:21AM Re: ERROR 1442 - Cannot update table which has
11 11:00:00 2009 -0700While working on MySQL triggers this afternoon, I encountered a strange problem that dealt with using a trigger to tweak records in a table after an UPDATE. Specifically, I http://mark.koli.ch/mysql-trigger-error-1442-hy000-cant-update-table-tbl-in-stored-functiontrigger-because-it-is-al wanted to write a trigger that would automatically loop over all records in a table, and then mark any “expired” if they were added more than X days ago. I http://forums.devshed.com/mysql-help-4/trigger-giving-error-1442-cant-update-table-593104.html added a trigger using the UPDATE mechanism I thought would work, but nope, I kept getting this error:ERROR 1442 (HY000): Can't update table 'pass' in stored function/trigger because it is mysql error already used by statement which invoked this stored function/trigger. Looks intimidating. According to the MySQL forums, a fairly large number of folks have reported this same problem. Sadly, I wasn’t able to find a good solution to the problem, and according to MySQL’s documentation, what I’m trying to do is damn near impossible with triggers.What I’m Trying To AccomplishI’ve got a mysql error 1442 table named “pass” that contains three fields: an ID field, a status, and a TIMESTAMP. The ID field is simply a unique ID number of some sort. The status field is an ENUM, either “active” or “expired”. And, the TIMESTAMP indicates when the record was added to the table. The goal here is to use a trigger that automatically sets the status to “expired” on each row that was added more than 7 days ago. I want to let the MySQL trigger engine take care of managing the expired status of these records for me, so that my web-app doesn’t explicitly have to.The SchemaHere’s the schema for my “pass” table:CREATE TABLE pass ( id BIGINT NOT NULL, status ENUM('active','expired') NOT NULL DEFAULT 'active', addedon TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ( id ) ) TYPE=InnoDB; Note that the default value on the status field is “active” and the default on the TIMESTAMP field is the CURRENT_TIMESTAMP (the current time on the MySQL server when the record was added to the table). Let’s add some records to this &ld
Search Username Password Remember Me? Register Lost Password? facebook google twitter rss Free Web Developer Tools Advanced Search Forum Databases MySQL Help Trigger giving error 1442: can't update table Thread: Trigger giving error 1442: can't update table Share This Thread Tweet This + 1 this Post To Linkedin Subscribe to this Thread Subscribe to This Thread February 25th, 2009,09:50 AM #1 No Profile Picture ihut View Profile View Forum Posts Contributing User Devshed Newbie (0 - 499 posts) Join Date Feb 2009 Posts 41 Rep Power 8 Trigger giving error 1442: can't update table I've made a table: create table testneed( aID int(10) not null auto_increment primary key, a int(10), b int(10) default 0 ); then I made a trigger: delimiter // create trigger needtest before update on testneed for each row begin update testneed set b=round(a/23); end; // delimiter ; insert into testneed(a) values(0); update testneed set a=350 where aID=1; and then I recieve the error: ERROR 1442 (HY000): Can't update table 'testneed' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. I've tried seting be in the trigger statement as update testneed set new.b=a/23; and I've tried make the trigger an insert as well as excluding begin and end from the trigger, but each time I recieve the same error. I would prefer to be able to do the trigger as an insert, but can use it as update if I have to. any tips are greatly appriciated Thanks Faq Reply With Quote February 25th, 2009,10:42 AM #2 No Profile Picture swampBoogie View Profile View Forum Posts Contributing User Devshed Regular (2000 - 2499 posts) Join Date Jan 2003 Location Paris Uppland Posts 2,402 Rep Power 393 Code: create trigger needtest before update on testneed for each row begin set new.b = round(new.a/23); end Faq Reply With Quote February 26th, 2009,09:06 AM #3 No Profile Picture ihut View Profile View Forum Posts Contributing User Devs