手把手教你mysql(十二)触发器
前言:
Mysql>CREATE TABLE t_grade(
id INT PRIMARY KEY AUTO_INCREMENT,
gradeName VARCHAR(20) NOT NULL,
studentCount INT NOT NULL)ENGINE=INNODB DEFAULT CHARSET=GBK;
Mysql>INSERT INTO t_grade VALUES(NULL,'一年级',0),(NULL,'二年级',0),(NULL,'三年级',0);
Mysql>CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
gradeId INT,
CONSTRAINT `fk_grade` FOREIGN KEY(`gradeId`) REFERENCES `t_grade`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=GBK;这是为大家准备的数据,如果遇到乱码请参考我的mysql中文乱码问题的文章.
一: 触发器的引入
触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT 语句、UPDATE 语句和DELETE 语句。
当数据库系统执行这些事件时，就会激活触发器执行相应的操作。
比如说有两张表有一张表是t_student 还有一张表是t_log当向t_student INSERT多条记录的时候,要求自动向t_log插入数据,这时候就要用到触发器
二: 创建与使用触发器
2.1 创建只有一个执行语句的触发器
CREATE TRIGGER 触发器名BEFORE | AFTER 触发事件
ON 表名FOR EACH ROW 执行语句
Before:在触发事件前执行触发器
After:在触发事件后执行触发器
触发事件:Insert,Update,Delete
例:创建一个名为trig_student的触发器,该触发器在每次向t_student表插入数据后更新t_grade表的学生人数
Mysql>DROP TRIGGER IF EXISTS `trig_student`;
Mysql>CREATE TRIGGER trig_student AFTER INSERT
ON t_student FOR EACH ROW
UPDATE t_grade SET studentCount=studentCount+1 WHERE new.gradeId=t_grade.id;在这边有new和old两个变量的概念
New:指新插入,更新的一条数据
Old:指刚删除的一条数据
创建完触发器后,我们插入一条数据,:
Mysql>INSERT INTO t_student VALUES(NULL,'张三',1);
Mysql>SELECT * FROM t_grade;接着,我们查询一下t_grade表,发现studentCount已经变了,这就是触发器的功能.
接下来,我们创建一个名为trig_student_delete的触发器,该触发器在每次向t_student表删除数据后更新t_grade表的学生人数
mysql>CREATE TRIGGER `trig_student_delete` AFTER DELETE
ON t_student FOR EACH ROW
UPDATE t_grade SET studentCount=studentCount-1 WHERE old.gradeId=t_grade.id;创建完触发器后,我们删除一条数据:
Mysql>DELETE FROM t_student WHERE id=1;
Mysql>SELECT * FROM t_grade;再查询一下t_grade表,发现studentCount又变了,触发器是不是很方便?
2.2 创建有多个执行语句的触发器
CREATE TRIGGER 触发器名BEFORE | AFTER 触发事件
ON 表名FOR EACH ROW
BEGIN
执行语句列表
END
例: 创建一个名为`trig2`的触发器当向t_student表插入数据后,更新t_grade表的数据并且向t_log表插入一条显示当前时间的记录.
首先,我们先创建t_log表:
Mysql>DROP TABLE IF EXISTS `t_log`;
Mysql>CREATE TABLE t_log(
id INT PRIMARY KEY AUTO_INCREMENT,
TIME DATETIME NOT NULL,
context VARCHAR(50) NOT NULL
);
然后删除之前创建的`trig_student`触发器
Mysql>DROP TRIGGER IF EXISTS `trig_student`;根据题目创建`trig2`触发器:
Mysql>DROP TRIGGER IF EXISTS `trig2`;
Mysql>DELIMITER |
CREATE TRIGGER `trig2` AFTER INSERT
ON `t_student` FOR EACH ROW
BEGIN
UPDATE t_grade SET studentCount=studentCount+1 WHERE new.gradeId=t_grade.id;
INSERT t_log VALUES(NULL,NOW(),'插入了一条student记录');
END;
| DELIMITER ;大家看,这边引入了一个新的概念DELIMITER,这代表分隔符,如果不加DELIMITER 那么这边会报错,并且向t_log表自动插入一条数据,这是因为分号的原因,分号代表结束,加了DELIMITER后就不会出现这种问题了,当有多条SQL语句时,记得加DELIMITER。
接下来,我们向t_student表插入一条数据,然后查看一下结果:
Mysql>INSERT INTO t_student VALUES(NULL,'张三',1);
跟刚刚一样,我们创建一个名为trig3的触发器,当删除学生记录后,会自动更新t_grade表的数据并且向t_log表插入一条有关时间的记录
首先删除trig_student_delete触发器
Mysql>DROP TRIGGER IF EXISTS `trig_student_delete`;根据题意创建trig3触发器:
Mysql>DELIMITER |
CREATE TRIGGER `trig3` AFTER DELETE
ON t_student FOR EACH ROW
BEGIN
UPDATE t_grade SET studentCount=studentCount-1 WHERE old.gradeId=t_grade.id;
INSERT INTO t_log VALUES(NULL,NOW(),CONCAT('删除了Id为',old.id,'的记录'));
END;
| DELIMITER ;接下来我们删除一条t_student数据,然后查看一下结果:
三：查看触发器
3.1 SHOW TRIGGERS 语句查看触发器信息
Mysql>SHOW TRIGGERS ;3.2 在triggers 表中查看触发器信息
Mysql>USE information_schema;
Mysql>SELECT * FROM TRIGGERS;
四：删除触发器
4.1 DROP TRIGGER 触发器名;
例: 删除名为trig3的触发器
Mysql>DROP TRIGGER `trig3`;五: 总结
今天为大家带来了触发器的使用,大家消化一下,下节为大家带来mysql的常用函数,谢谢大家!
