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 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 after insert Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it
After Update Trigger In Mysql
only takes a minute: Sign up MySQL Trigger cannot update table - getting ERROR 1442 up vote 4 down vote favorite 1 I have the following trigger: CREATE TRIGGER sum AFTER INSERT ON news FOR EACH ROW UPDATE news SET updating of new row is not allowed in after trigger NEW.sum = (NEW.int_views + NEW.ext_views)/NEW.pageviews It sums the int_views and ext_views column of a table and divides them by the total pageviews. Whenever I try to add a new row to news, I get the following error: ERROR 1442 (HY000) at line 3: Can't update table 'news' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. The trigger seems pretty simple to me. Is there a reason why the trigger fails to run? mysql mysql create trigger share|improve this question asked Aug 30 '12 at 19:13 egidra 1,51773171 no where condition? –jcho360 Aug 30 '12 at 19:17 @jcho360, Sorry, I'm not seeing where to include a where condition. I want the trigger to execute on every row after it's inserted. –egidra Aug 30 '12 at 19:19 if that doesn't works let me know to try with the WHERE news_id = NEW.news_id; at the end –jcho360 Aug 30 '12 at 19:30 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote accepted The symptom is, that you are running an UPDATE (for all rows) inside a INSERT trigger - both modify the table, which is not allowed. That said, if I guess the intention of your trigger correctly, you do not want to update all rows, but only the newly inserted row. You can achieve that easily with CREATE TRIGGER sum BEFORE INSERT ON news FOR EACH ROW SET NEW.sum = (NEW.int_views + NEW.ext_views)/NEW.pageviews Mind that this is a BEFORE INSERT trigger, as you want to change the row before it is written to the table. share|improve this answer answered Aug 30 '12 at 19:22 Eugen Rieck 42.3k33859 add a comment| up vote 2 down vote If you try to update/insert on the same table that cause trigger to fire do not use the common sql command like -> UPDATE TABLE_NAME SET COLUMN_NAME = VALUE WHERE CONDITION
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 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up is there any solution for MYSQL Error Code: 1442? http://stackoverflow.com/questions/12203859/mysql-trigger-cannot-update-table-getting-error-1442 up vote 0 down vote favorite my code is very long so i'm gonna make it simple. i have the following: table1 --> have a trigger on insert (call function1 to insert into table2) table2 --> have a trigger on insert (call function2) function2() update table1 depending on a query that table1 is related to. and i get error can't update table table1 in stored function/trigger because it http://stackoverflow.com/questions/8871728/is-there-any-solution-for-mysql-error-code-1442 is already used by statement which invoked this.... so is there any solution or workaround for this... mysql sql share|improve this question asked Jan 15 '12 at 17:27 medo ampir 61941534 Are you saying the trigger on table2 inserts records in to table1? –Tony Hopkinson Jan 15 '12 at 18:42 no, it is running a function that updates table1 –medo ampir Jan 15 '12 at 18:57 When the actual code is large (really, anytime you're posting code), you can always produce a minimal sample case instead of a vague diagram. –outis Jan 16 '12 at 1:21 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote The thing to understand about triggers is they are part of the transaction. So Insert Table 1, triggers insert table 2 which updates table1, could cause the original insert to bump into itself. Not done triggers with MySQl, in theory this could be okay, if it's attemting to update the record originally inserted, which hasn't actually been committed yet.... Try and avoid the situation is the usual best advice, however if you can't, an intermediate table might work (insert into that and have it tri
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 http://forums.devshed.com/mysql-help-4/trigger-giving-error-1442-cant-update-table-593104.html 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 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/ 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, error number 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 error number 1442 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 Devshed Newbie (0 - 499 posts) Join Date Feb 2009 Posts 41 Rep Power 8 Originally Posted by swampBoogie Code: create trigger needtest before update on testneed for each row begin set new.b = round(new.a/23); end Thank you very much! Faq Reply With Quote Share This Thread Tweet This + 1 this Post To Linkedin Subscribe to this Thread Subscribe to Th
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 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 ; ΓΆβ‚