MySQL创建触发器样例

# init
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS class;

# 创建测试用的班级表
CREATE TABLE class (
    class_id VARCHAR(10) PRIMARY KEY,
    stu_cnt INT(32) DEFAULT 0
);

# 创建测试用的学生表
CREATE TABLE students (
    id INT(32) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    class_id VARCHAR(10),
    sname VARCHAR(20),
    CONSTRAINT fk_students_class_id FOREIGN KEY (class_id) REFERENCES class (class_id)
);

# 创建触发器tg_students
CREATE TRIGGER tg_students
AFTER INSERT 
ON students FOR EACH ROW
BEGIN 
DECLARE c int;
SELECT stu_cnt INTO c FROM class WHERE class_id=NEW.class_id;
UPDATE class SET stu_cnt=c+1 WHERE class_id=NEW.class_id;
end;

# 创建班级的测试数据
INSERT INTO class (class_id) VALUES ('c101');
INSERT INTO class (class_id) VALUES ('c102');
INSERT INTO students (class_id,sname) VALUES ('c101', 'zifeiy');
INSERT INTO students (class_id,sname) VALUES ('c102', 'feifei');
INSERT INTO students (class_id,sname) VALUES ('c101', 'feiyu');

# 查看结果
SELECT * from class

在这个测试样例中创建了一个student表，和一个class表，每当我在student表中新增一个学生，class表终stu_cnt这个字段就会新增1。

转载于:https://www.cnblogs.com/zifeiy/p/9040832.html