MySQL Bugs: #63416: Trigger can't manipulate its parent table

Bug #63416	Trigger can't manipulate its parent table
Submitted:	24 Nov 2011 10:53	Modified:	25 Nov 2011 17:52
Reporter:	Joe Borg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.77	OS:	Linux (CentOS 5)
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, PROCEDURE, trigger

Description:
Trying to make a trigger update it's parent AFTER INSERT causes this error:
#1442 - Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 

How to repeat:
CREATE FUNCTION calculate_this(var_id INT, var_param1 CHAR(12), var_param2 CHAR(12))
RETURNS CHAR(16)
NOT DETERMINISTIC
BEGIN
DECLARE var_result CHAR(16);
SET var_result = var_param1 * var_param2;
RETURN var_result;
END

CREATE TRIGGER calculate_this_on_update
AFTER INSERT ON `tbl` 
FOR EACH ROW
BEGIN
UPDATE `tbl`
SET `calculated_param` = calculate_this(NEW.id,NEW.param1,NEW.param2)
WHERE `id` = NEW.id;
END

Suggested fix:
Allow this to happen?

Simplified and complete test case that works on both MySQL 5.0.90 and 5.1.60 (but I may have missed a point here as regards details of the TRIGGER in your case): 

DROP DATABASE IF EXISTS fktest; 
CREATE DATABASE fktest;
USE fktest;

CREATE TABLE parent (id INT PRIMARY KEY AUTO_INCREMENT, txt VARCHAR(10));
INSERT INTO `parent` (`txt`) VALUES('bluh');

CREATE TABLE `child` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `outside` INT,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_child` FOREIGN KEY (`outside`) REFERENCES `parent` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

DELIMITER $$
CREATE
    FUNCTION `fktest`.`const`()
    RETURNS VARCHAR(10)
    BEGIN
	RETURN 'blah';
    END$$
DELIMITER ;

DELIMITER $$
CREATE
    TRIGGER `fktest`.`insertconst` AFTER INSERT ON `fktest`.`child`
    FOR EACH ROW BEGIN
    UPDATE `parent` SET `txt` = `fktest`.`const`();
    END $$
DELIMITER ;

INSERT INTO `child` (outside) VALUES (1);

SELECT * FROM `parent`;
/*

    id  txt   
------  ------
     1  blah  
*/  

Peter
(not a MySQL person)

This is a documented limitation (see http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html):

"Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."

If you ask for this to be implemented, there is bug #29542 (and its numerous duplicates) already...

Thanks Valeriy Kravchuk, do you know of any work around?

Is this going to be added soon?

I've just realized; this can be done easily with BEFORE INSERT / UPDATE.

My stupidity.