mysql - phpmyadmin 中的触发器出现问题

Question

我的触发器有问题，如果删除帐单信息，它应该将表状态设置回空

DROP TRIGGER IF EXISTS `DeleteBillInfo`;CREATE DEFINER=`root`@`localhost` TRIGGER `DeleteBillInfo` AFTER DELETE ON `bill_info` FOR EACH ROW 
BEGIN

DECLARE billInfoID INT(11);
DECLARE billID INT(11);
DECLARE idTable INT(11);
DECLARE count INT(11);
SET count = 0;

SELECT old.id INTO billInfoID FROM bill_info;
SELECT old.id_bill INTO billID FROM bill_info;

SELECT id_table INTO idTable FROM bill WHERE id = billID;

SELECT COUNT(*) INTO count FROM bill_info AS bi, bill AS b WHERE b.id= bi.id_bill AND b.id= billID AND b.status = 0;
IF count=0 THEN
UPDATE tablefood SET status= N'Empty' WHERE id= idTable;
END IF;

END

当我尝试删除一行时得到这个

#1172 - result consisted of more than one row

我应该做什么来解决这个问题？

Answer 1

SELECT old.id INTO billInfoID FROM bill_info;

为 bill_info 表中的每一行返回一行，但您只能将一行中的值分配给变量。无需为此使用 SELECT，您只需使用:

SET billInfoID = old.id;
SET billID = old.id_bill;