创建以下触发器并执行删除操作后出错1242(21000)
创建以下触发器并执行删除操作后出错1242(21000)
提问于 2021-01-13 13:24:33
我想要创建一个触发器，它将记录从可主程序复制到卸载堆栈，然后在可维护表中删除它，并且Undo堆栈表将执行5条记录空间的堆栈，并且该触发器执行推送操作。
这是我的扳机
CREATE TRIGGER undo_op
BEFORE DELETE ON maintable FOR EACH ROW
BEGIN当解堆栈未满时
IF (SELECT COUNT(*) FROM (SELECT * FROM undostack)u)<5 THEN
INSERT INTO undostack VALUES(((SELECT COUNT(*) FROM undostack)+1),
(SELECT id FROM maintable WHERE id=OLD.id),
(SELECT descp FROM maintable WHERE descp=OLD.descp),
(SELECT cat FROM maintable WHERE cat=OLD.cat),
(SELECT dat FROM maintable WHERE dat=OLD.dat),
(SELECT amt FROM maintable WHERE amt=OLD.amt));当卸载堆栈已满且必须删除通过更新输入的最旧记录时。在这里，undo_no充当指针。
ELSE1<-2
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=2)
WHERE undo_no=1;2<-3
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=3),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=3),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=3)
WHERE undo_no=2;3<-4
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=4),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=4),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=4)
WHERE undo_no=3;4<-5
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=5),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=5),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=5)
WHERE undo_no=4;5<-使用要从主程序中删除的值进行更新
UPDATE undostack SET
id=(SELECT id FROM maintable WHERE id=OLD.id),
descp=(SELECT descp FROM maintable WHERE descp=OLD.descp),
cat=(SELECT id FROM maintable WHERE cat=OLD.cat),
dat=(SELECT id FROM maintable WHERE dat=OLD.dat),
amt=(SELECT amt FROM maintable WHERE amt=OLD.amt)
WHERE undo_no=5;
END IF;
END触发器没有错误，但当我执行
DELETE FROM maintable WHERE id=10;有以下错误
错误1242 (21000)：子查询返回多于1行
请帮我解决这个错误。
我使用的是MySQL 5.7.3
回答 1
Database Administration用户
发布于 2021-01-13 16:13:29
最简单的方法是(例如undo_no 1)。
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack) u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack) u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack) u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack) u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack) u WHERE undo_no=2)
WHERE undo_no=1;通过使用(SELECT * FROM undostack) u，Mysql生成一个用于选择数据的表。
很遗憾，我想在您的折叠代码中做得更好，但是五行是非常简单的。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/283151
复制相关文章
相似问题
腾讯云开发者
