mysql 触发器创建软件_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。