Can't update table 'test_trigger' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

[Err] 1442 - Can't update table 'test_trigger' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

 

DROP TABLE IF EXISTS `test_trigger`;
CREATE TABLE `test_trigger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jb` double DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `dtype` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
DROP TRIGGER IF EXISTS `t_trigger`;
DELIMITER ;;
CREATE TRIGGER `t_trigger` BEFORE INSERT ON `test_trigger` FOR EACH ROW BEGIN
    -- sum jb..

IF new.dtype = 'touch' THEN
    -- get sum row id 
SET @tmp_id = NULL ; SELECT
    MAX(id) INTO @tmp_id
FROM
    test_trigger
WHERE
    `name` = NEW.`name`
AND dtype = 'sum' ; -- insert or update  row 

 IF @tmp_id IS NULL THEN
     insert into test_trigger (jb,dtype) values( new.jb ,'sum') ;
ELSE
    UPDATE test_trigger SET jb = jb +new.jb WHERE id = @tmp_id;
END IF;

END
IF ; 


END
;;
DELIMITER ;

 

[Err] 1442 - Can't update table 'test_trigger' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

 

导致原因是 

　　在一张表中的触发器，不能直接对同一张表执行 增加，删除，修改操作，防止造成死循环（个人理解）。
借助连接查询语句试试看，实现思路，先将要符合删除条件的记录查询出来，作为一个新的集合（相当于一张中间表），在通过集合与当前表的 inner join  语句 进行删除 (记得之前用过，好像可以，记得不是很清楚了)