报错信息如下:
ERROR 1442 (HY000): Can't update table 'event1' in stored function/trigger
because it is already used by statement which invoked this stored function/trigger.
个人理解是因为在更新读取表A的时候,触发器又要读取表A的数据然后再更新表A。
如下面的代码:
CREATE TRIGGER `updateSelfStatusUpdate` BEFORE UPDATE ON `member` FOR EACH ROW BEGIN
SET @id:=NEW.`id`;
SET @type:=NEW.`type`;
SET @value_new:=NEW.`value`;
SET @value_old:=OLD.`value`;
IF @type=1 THEN
IF @value_old>0 AND @value_new=0 THEN
#UPDATE `member` SET `status`=0 WHERE `id`=@id;
SET NEW.`status`=0;
ELSEIF @value_old=0 AND @value_new>0 THEN
#UPDATE `member` SET `status`=1 WHERE `id`=@id;
SET NEW.`status`=1;
END IF;
END IF;
END;
上面注释掉的UPDATE语句就是问题所在,即在表member的更新触发器里使用UPDATE语句更新member表本身。
解决方案是使用
SET NEW.`field`=xxx
这种语句来代替,同样能实现