Mysql Error 1422
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 mysql call stored procedure from function Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs mysql stored procedure return value Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, create procedure mysql just like you, helping each other. Join them; it only takes a minute: Sign up Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger up vote 7 down vote favorite Everywhere mysql triggers I look it seems MySQL stored procedures can do transactions. Yet when I declare my stored function create function test( a int ) returns int MODIFIES SQL DATA BEGIN START TRANSACTION ; update t set col='some value' where id=a ; COMMIT ; return 0 ; END // I get Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger. mysql stored-procedures stored-functions share|improve this question asked Jun 6 '13 at 18:49 bobobobo 27.4k32184243 add a comment| 1 Answer 1 active oldest votes up vote 8 down vote accepted Actually you are not allowed transactions inside stored functions. You are allowed transactions inside stored procedures only. create procedure test( a int ) MODIFIES SQL DATA BEGIN START TRANSACTION ; update t set col='some value' where id=a ; COMMIT ; END // To return values from the SP, use output parameters or use the result set from the last select statement in the SP. share|improve this answer answered Jun 6 '13 at 18:49 bobobobo 27.4k32184243 5 Also worth noting is that if you call the procedure from within a trigger, you will still be rewarded with an 'Explicit or implicit commit is not allowed in stored function or trigger' error. Frustrating. –aroth Jan 24 '14 at 6:27 hey, this is a bit old, but I'm running into this problem right now. Is there any way around it? –user2223059 Nov 5 '15 at 20:18 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of servi
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 them; it only takes a minute: http://stackoverflow.com/questions/16969875/error-code-1422-explicit-or-implicit-commit-is-not-allowed-in-stored-function Sign up MySQL Error when trigger calls a procedure up vote 2 down vote favorite I have a table (ft_ttd) and want to sort it descending (num) and insert rating numbers into rating column. Something like that: I've created a procedure. CREATE PROCEDURE proc_ft_ttd_sort BEGIN CREATE TEMPORARY TABLE ft_ttd_sort (id int (2), num int (3), rating int (2) AUTO_INCREMENT PRIMARY KEY); INSERT INTO http://stackoverflow.com/questions/8051431/mysql-error-when-trigger-calls-a-procedure ft_ttd_sort (id, num) SELECT id, num FROM ft_ttd ORDER BY num DESC; TRUNCATE TABLE ft_ttd; INSERT INTO ft_ttd SELECT * FROM ft_ttd_sort; DROP TABLE ft_ttd_sort; END; When I call it - it works great. CALL proc_ft_ttd_sort; After that I've created trigger calling this procedure. CREATE TRIGGER au_ft_ttd_fer AFTER UPDATE ON ft_ttd FOR EACH ROW BEGIN CALL proc_ft_ttd_sort(); END; Now every time when I update ft_ttd table I've got a error. UPDATE ft_ttd SET num = 9 WHERE id = 3; ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function ortrigger. Any ideas how to make it work? Maybe this process can be optimized? Thank you! mysql sql database triggers share|improve this question edited Nov 8 '11 at 20:02 pilcrow 32.2k55898 asked Nov 8 '11 at 13:51 Oleg Legun 1313 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote accepted Triggers can't do it DDL aside, your trigger-based approach has a few difficulties. First, you want to modify the very table that's been updated, and that's not permitted in MySQL 5. Second, you really want a sta
Community Podcasts MySQL.com Downloads Documentation Section Menu: MySQL Forums :: Triggers :: error 1422 when create trigger New Topic Advanced http://forums.mysql.com/read.php?99,505198,505198 Search error 1422 when create trigger Posted by: tra huu () Date: December 09, 2011 12:10PM DELIMITER $$ drop trigger if exists abc $$ create trigger acb AFTER INSERT on sach FOR EACH ROW begin set @ma = (select madanhmuc from inserted) ; set @tam = (select count(*) from sach s where s.madanhmuc stored procedure = ma); if tam > 1 then rollback ; end if; end $$ when execute export error: Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger. Navigate:Previous Message•Next Message Options:Reply•Quote Subject Views Written By Posted error 1422 when create trigger 3033 tra huu 12/09/2011 12:10PM Re: error mysql error 1422 1422 when create trigger 1187 Peter Brawley 12/09/2011 03:46PM Re: error 1422 when create trigger 1013 tra huu 12/10/2011 06:18AM Re: error 1422 when create trigger 1017 Peter Brawley 12/10/2011 10:14AM 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. Contact Sales USA: +1-866-221-0634 Canada: +1-866-221-0634 Germany: +49 89 143 01280 France: +33 1 57 60 83 57 Italy: +39 02 249 59 120 UK: +44 207 553 8447 Japan: 0120-065556 China: 10800-811-0823 India: 0008001005870 More Countries» Contact Us Online» Products MySQL Enterprise Edition MySQL Standard Edition MySQL Classic Edition MySQL Cluster CGE MySQL Embedded (OEM/ISV) Services Training Certification Consulting Support Downloads MySQL Community Server MySQL Cluster MySQL Fabric MySQL Utilities MySQL Workbench About MySQL Contact Us How to