MySQL触发器的创建删除

?

1 2 3 4 5 6 7

mysql>  CREATE TABLE class (                                                          `id` int NOT NULL AUTO_INCREMENT,                                                `name` varchar(128) DEFAULT NULL,                                                 `teacher` varchar(64) DEFAULT NULL,    `count`  int DEFAULT 0,                                             UNIQUE KEY `id` (`id`)                                                              );  #创建班级表 class   insert into class values(101, '萌新一班', 'Martin', 0),(102, '萌新二班', 'Rock', 0),(103, '萌新三班', 'Janny', 0);

　

#创建学生表

CREATE TABLE `student` (                                                  
  `id` int NOT NULL AUTO_INCREMENT UNIQUE,                                                            
  `name` varchar(64) DEFAULT NULL,                                                
  `class_id` int DEFAULT NULL,                                                      
  `sex` enum('F','M') DEFAULT NULL                                                  
); 

 

#创建触发器，学生增加则count增一

create trigger tri_insert_student after insert on student for each row update class set count=count+1 where class.id = NEW.class_id; 

 

#向学生表增加多条数据

 insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F'); 

 

#创建触发器，删除学生count减一

create trigger tri_delete_student after delete on student for each row update class set count=count-1 where id = OLD.class_id; 

 

 #删除触发器

 DROP TRIGGER trigger_name;