如题:mysql更新触发器先插入另一张表然后删除该条数据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表的插入触发器中也不行。在线等。。
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表的插入触发器中也不行。在线等。。
解决方案 »
- MySQL数据库主从同步问题
- 求mysql优化查询思路
- 求解 mysql如何查找某字段具体值所在表
- mysql 如何实现select ... where a.id *= b.id这种语句
- 哪里可以下载mysql的源代码,请把详细地址贴出来?
- WWWWA ,,时间函数问T续,,,,,
- 关于连接mysql的问题
- 百分请教:### 通过PHP连接MySQL失败 ###
- 一个MySql多表联合查询问题,小白在线求指导!~~大神们给指点迷经啊!~~~
- mysql限制条件下的多列查询,求助大神!
- MYSQL CPU达90%之多,如何去查看资源的占用情况?
- 采用OpenReplicator解析MySQL binlog遇到的问题
可以另外创建过程完成需求
我刚才测试(MYSQL 5.7),设置@disable_trigger=1,没用,根本没这个参数。
我想通过CALL 存储过程做删除,发现还是报一样的错误。
可能这些操作在触发器里都是一个事务完成的。