Hassan Tahir Hassan Tahir - 2 years ago 66
SQL Question

triiger giving syntax error Please help me out

DROP TRIGGER IF EXISTS demo_inc_when_viewstatus_one ;

CREATE TRIGGER demo_inc_when_viewstatus_one AFTER UPDATE ON `tbl_ffa_demo`
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 $$

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 Source

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, ...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download