drop trigger if exists trigger_tb_carparkinoutrecord_update;
CREATE TRIGGER trigger_tb_carparkinoutrecord_update AFTER UPDATE ON tb_carparkinoutrecord
FOR EACH ROW
BEGIN
IF @disable_trigger IS NULL THEN
IF new.ProcessingType not in ('未处理主记录','未处理辅记录') THEN
INSERT INTO tb_carparkinoutrecord_history SELECT temp_tb_carparkinoutrecord.*,
(CASE WHEN ( isnull(b.CarOwnerName) OR (b.CarOwnerName = '')) THEN '临时车' ELSE b.CarOwnerName END ) AS CarOwnerName
,( CASE WHEN ( isnull(c.GroupName) OR (c.GroupName = '') ) THEN ' ' ELSE c.GroupName END ) AS GroupName,
(SELECT GetParentGroupName (b.CarGroupGuid) AS Expr1) AS FullGroupName
FROM
( SELECT * FROM tb_carparkinoutrecord as a where a.guid = new.guid) as temp_tb_carparkinoutrecord
LEFT JOIN tb_carinfo as b on ((temp_tb_carparkinoutrecord.InCarNO = b.CarNo) AND (temp_tb_carparkinoutrecord.CarColor = b.CarColor))
LEFT JOIN tb_cargroup as c on (b.CarGroupGuid = c.guid);
IF ROW_COUNT()>0 THEN
DELETE FROM tb_carparkinoutrecord WHERE guid = new.guid;
END IF;
END IF;
SET @disable_trigger = NULL;
END IF;
END
删除数据时会报错,报错内容:Can't update table 'tb_carparkinoutrecord' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
请问这个应该怎么处理,我把删除语句放在tb_carparkinoutrecord_history表的插入触发器中也不行。在线等。。
4 个解决方案
#1
如果你在触发器里面对刚刚插入的数据进行了 insert/update, 则出现这个问题。因为会造成循环的调用.
可以另外创建过程完成需求
可以另外创建过程完成需求
#2
我是在update时触发的,触发内容是把update的数据插入另一张表,然后删除该条数据
#3
if 里添加@disable_trigger=1,在执行过程中不再执行这个触发器
#4
msyql 应该不支持,你触发器中删除的表是触发器所在的表
#1
如果你在触发器里面对刚刚插入的数据进行了 insert/update, 则出现这个问题。因为会造成循环的调用.
可以另外创建过程完成需求
可以另外创建过程完成需求
#2
我是在update时触发的,触发内容是把update的数据插入另一张表,然后删除该条数据
#3
if 里添加@disable_trigger=1,在执行过程中不再执行这个触发器
#4
msyql 应该不支持,你触发器中删除的表是触发器所在的表