vendredi 30 octobre 2020

How to create IF condition in trigger mysql when condition is result of count table

Trigger in mysql

BEGIN

DECLARE jml INT;

SET jml = (SELECT COUNT(lapor_karya.ID_Lapor) FROM lapor_karya GROUP BY NEW.ID_Karya);

IF jml>10 THEN 

DELETE * FROM karya_pelajar WHERE ID_Karya=NEW.ID_Karya;

END IF;

END

Error

The following query has failed: "CREATE DEFINER=`root`@`localhost` TRIGGER `delete_karyabanned` AFTER INSERT ON `lapor_karya` FOR EACH ROW BEGIN DECLARE jml INT; SET jml = (SELECT COUNT(lapor_karya.ID_Lapor) FROM lapor_karya GROUP BY NEW.ID_Karya); IF jml>10 THEN DELETE * FROM karya_pelajar WHERE ID_Karya=NEW.ID_Karya; END IF; END"
MySQL menyatakan: #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

I want to get count of my query, and then if the result of count is more than 10, it will execute query delete. But my code doesn't working. I use mysql database

Aucun commentaire:

Enregistrer un commentaire