本来我是用navicat for mysql 在本地测试,添加触发器是OK的。
但是在服务器时 我用phpmyadmin 添加,报语法错误,才知道phpmyadmin是添加不了触发器的。
改用navicat for mysql 工具就可以了
触发器语法和SQL Server有所不同,变量的赋值之类的,下面是语法案例
DROP TRIGGER IF EXISTS exam_trigger;
create trigger exam_trigger
after insert on exam
FOR EACH ROW
BEGIN
DECLARE peoplecount int;
DECLARE newclasssize int;
DECLARE newmodifycount int;
select count(*) INTO peoplecount from exam where examschool = NEW.examschool and examnum = NEW.examnum;
select classsize,modifycount INTO newclasssize,newmodifycount from examschool where eschool = NEW.examschool;
IF(peoplecount = newclasssize*21) THEN
IF(NEW.examschool='翠竹' || NEW.examschool='园岭') THEN
IF(newmodifycount<=1) THEN
update examschool set classsize=classsize+2,modifycount=modifycount+1 where eschool = NEW.examschool;
ELSEIF(newmodifycount=2) THEN
update examschool set classsize=classsize+1,modifycount=modifycount+1 where eschool = NEW.examschool;
END IF;
ELSEIF(NEW.examschool='莲花北') THEN
IF(newmodifycount=0) THEN
update examschool set classsize=classsize+2,modifycount=modifycount+1 where eschool = NEW.examschool;
ELSEIF(newmodifycount=1) THEN
update examschool set classsize=classsize+1,modifycount=modifycount+1 where eschool = NEW.examschool;
END IF;
ELSEIF(NEW.examschool='益田' && newmodifycount<=2) THEN
update examschool set classsize=classsize+1,modifycount=modifycount+1 where eschool = NEW.examschool;
ELSEIF(NEW.examschool='景田' && newmodifycount<=4) THEN
update examschool set classsize=classsize+1,modifycount=modifycount+1 where eschool = NEW.examschool;
END IF;
END IF;
END