Hassan Tahir Hassan Tahir - 6 months ago 9
SQL Question

triiger giving syntax error Please help me out

DROP TRIGGER IF EXISTS demo_inc_when_viewstatus_one ;
DELIMITER $$

CREATE TRIGGER demo_inc_when_viewstatus_one AFTER UPDATE ON `tbl_ffa_demo`
FOR EACH ROW BEGIN
UPDATE `tbl_stats` SET open_demos=CASE WHEN NEW.demo_status=1 THEN
open_demos+1 ,
total_demos=total_demos+1 WHERE tbl_stats.area_id=NEW.territory AND NEW.view_status=1
END $$
DELIMITER;


The error message I received was:


1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' total_demos=total_demos+1 WHERE tbl_stats.area_id=NEW.territory AND NE' at line 4

Answer

You did not follow the syntax for the case statement:

case when <condition> then <true branch> else <false branch> end

So, you should have something like:

...  SET open_demos=CASE WHEN NEW.demo_status=1
     THEN open_demos+1 ELSE open_demos=open_demos END, ...