用phpmyadmin添加不了触发器

最新推荐文章于 2023-12-30 14:35:30 发布

原创最新推荐文章于 2023-12-30 14:35:30 发布 · 3.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

MySQL 专栏收录该内容

0 篇文章

订阅专栏

本文介绍了一种使用MySQL触发器的方法，特别关注于在插入操作之后调整学校班级大小的逻辑。通过具体的代码示例展示了如何根据不同条件更新学校班级的大小。

本来我是用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