Mysql – Error 1242(21000) after creating following trigger and performing delete operation

database-designdbmsmysql-5.7

I want to create a trigger which copies the record from maintable to undostack before deleting it in maintable and undostack table acts a stack of 5 records space and this trigger performs a push operation.

This is my trigger

CREATE TRIGGER undo_op
BEFORE DELETE ON maintable FOR EACH ROW
BEGIN

When undostack is not full

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));

When undostack is full and has to delete the oldest record entered by updating. here undo_no acts as pointer.

ELSE

1<-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 with the values which are to be deleted from maintable

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

The trigger has no errors
but when I execute

DELETE FROM maintable WHERE id=10;

There is following error

ERROR 1242 (21000):Subquery returns more than 1 row

Please help me with this error

I'm using MySQL 5.7.3

Best Answer

The easiest way to do this, is(for example 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;

By using (SELECT * FROM undostack) u Mysql generates a table which it uses to select your data.

usully i would say make this better in your pplication code, but five rows is very samll

Related Question