DELIMITER $$
CREATE TRIGGER cek
AFTER INSERT ON lapor_karya
FOR EACH ROW
BEGIN
SET @var = (SELECT COUNT(lapor_karya.ID_Lapor) FROM lapor_karya GROUP BY NEW.ID_Karya);
IF(@var > 10)
THEN
DELETE * FROM karya_pelajar WHERE ID_Karya=NEW.ID_Karya;
END IF;
END $$
DELIMITER ;
错误
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* FROM karya_pelajar WHERE ID_Karya=NEW.ID_Karya;
END IF;
END' at line 9
我想从lapor_karya表中获取值,并将该值用于条件IF。如果我的值超过10,则将执行查询删除。但它不起作用
关于DELETE
语句的错误。在编写DELETE
语句时,您必须离开*
。
另外,试着不用括号。
IF @var > 10 THEN
DELETE FROM karya_pelajar WHERE ID_Karya=NEW.ID_Karya;
END IF;
有关更多信息,请查看MySQL文档。
稍微简化一下:
BEGIN
IF ( SELECT COUNT(*) FROM lapor_karya
WHERE ??? = NEW.ID_Karya
) > 10
THEN
DELETE * FROM karya_pelajar WHERE ID_Karya=NEW.ID_Karya;
END IF;
END $$
也就是说,返回单个值的带括号的SELECT
实际上可以在任何可以使用表达式的地方使用。