MySQL触发器

目录

7.1 触发器

7.1.1 触发器简介

7.1.2 触发器的创建

7.1.3 示例

---

7.1 触发器

7.1.1 触发器简介

触发器是一种比较特殊的存储过程，它的执行不是由程序调用，也不是手工调用，而是通过事件来进行触发。

比如说 对一张表 进行（增，删，改），去激活它的执行。

触发器经常应用在加强数据完整性，和业务规则中，如：当一个学生表中添加了一个学生信息时，那么对应的学生数目肯定会有所改变。像这样的情况，我们就可以针对学生表创建一个触发器：以确保每次增加一个学生记录时，就执行一次关于学生总数的计算操作，从而确保学生总数与记录数的一致性。

7.1.2 触发器的创建

#语法要求
CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件
    ON 表名 FOR EACH ROW
    BEGIN
        触发器程序体;
    END 
    
#说明
<触发器名称> 参考索引命名||表命名
BEFORE|AFTER   触发器时机
INSERT DELETE UPDATE 触发的事件（分别是插入、删除、更新）
ON 表名   在哪张表上建立触发器 
FOR EACH ROW 触发器执行间隔策略 ===> 字句通知触发器,每隔一行执行一次动作,并不是对整个表执行。
触发器程序体  要出发的SQL语句

7.1.3 示例

#当前有两张表 student   student_count

#示例1：每当学生表中有记录增加(减少)时那么学生行数的这个表,需要进行最新的行数统计
1.创建表（建在了myshool_db里）
CREATE TABLE student(
    id int auto_increment primary key not null,
    name varchar(40)
);
​
CREATE TABLE student_count(
    totle int
);
#添加一条学生信息
INSERT INTO student (name) VALUES('jack_1');
INSERT INTO student_count VALUES(1);


mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | jack_1 |
+----+--------+
mysql> select * from student_count;
+-------+
| totle |
+-------+
|     1 |
+-------+

这样手动往添加student表里一条学生信息，同样还要手动条件更新student_count表的行数统计，操作太繁琐了，我们可以创建一个触发器，在往student表里添加学生信息后自动更新行数统计。

#而student_count语句全都会变为UPDATE格式

CREATE TRIGGER student_insert_trigger 
AFTER INSERT ON student 
FOR EACH ROW
    BEGIN 
        UPDATE student_count SET totle = totle+1;
    END;    
    
（注意：直接把上面创建触发器的命令不能直接粘贴到xshell的mysql的输入命令行中，这种方法无法执行，会报错，需打开Navicat Premium 15 应用中新建查询输入才可执行。）
#查看触发器是否已存在
    SHOW TRIGGERS;

#我们插入若干条数据进行测试
INSERT INTO student (name) VALUES('jack_2'),('jack_3'),('jack_4'),('jack_5'),('jack_6'),('jack_7'),('jack_8'),('jack_9'),('jack_10');

mysql> SELECT * FROM student_count;
+-------+
| totle |
+-------+
|    10 |
+-------+

​
#套用语法完成删除的触发器
 

CREATE TRIGGER student_delete_trigger 
AFTER DELETE
    ON student FOR EACH ROW
    BEGIN
        UPDATE student_count SET totle = totle-1;
    END;
​

#数据删除数据进行测试：

mysql> SELECT * FROM student_count;
+-------+
| totle |
+-------+
|    10 |
+-------+

mysql> SELECT * FROM student;
+----+---------+
| id | name    |
+----+---------+
|  1 | jack_1  |
|  2 | jack_2  |
|  3 | jack_3  |
|  4 | jack_4  |
|  5 | jack_5  |
|  6 | jack_6  |
|  7 | jack_7  |
|  8 | jack_8  |
|  9 | jack_9  |
| 10 | jack_10 |
+----+---------+

mysql> DELETE FROM student WHERE id IN (5,6,7,8,9,10);
mysql> SELECT * FROM student_count;
+-------+
| totle |
+-------+
|     4 |
+-------+

#示例2：职员表 (工号,姓名,性别,年龄)  <===>  工资表 (工资编号,姓名,工资5000)    
对职工进行添加时 工资表中也要体现当前职工的信息
对职工进行修改时 工资表中也要一并修改当前职工的信息
对职工进行解聘时 工资表中也要一并删除当前员工的工资信息

#创建职工表

CREATE TABLE tab11(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sex ENUM('m','f'),
    age INT
);

#创建工资表

CREATE TABLE tab22(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    salary DOUBLE(10,2)
);

#创建触发器1  INSERT  对职工进行添加时 工资表中也要体现当前职工的信息

CREATE TRIGGER tab11_insert_trigger 
    AFTER INSERT ON tab11
    FOR EACH ROW
    BEGIN
        INSERT INTO tab22 VALUES (NULL,new.name,5000);
    END;
​

测试用户新增  

INSERT INTO tab11 VALUES (1,'AA','f',18);
INSERT INTO tab11 VALUES (2,'BB','m',20);
INSERT INTO tab11 VALUES (3,'CC','m',18);

mysql> SELECT * FROM tab22;
+----+------+---------+
| id | name | salary  |
+----+------+---------+
|  1 | AA   | 5000.00 |
|  2 | BB   | 5000.00 |
|  3 | CC   | 5000.00 |
+----+------+---------+

新增完成后我们检查tab2会发现有变化

#创建触发器2  UPDATE  对职工进行修改时 工资表中也要刷新当前职工的信息

CREATE TRIGGER tab11_update_trigger
    AFTER UPDATE ON tab11
    FOR EACH ROW
    BEGIN
        UPDATE tab22 SET name = new.name WHERE name = old.name;
    END;

测试用户修改

mysql> SELECT * FROM tab22;
+----+------+---------+
| id | name | salary  |
+----+------+---------+
|  1 | AA   | 5000.00 |
|  2 | BB   | 5000.00 |
|  3 | CC   | 5000.00 |
+----+------+---------+

UPDATE tab11 SET name = 'FF' WHERE name = 'AA';
“SET name = 'FF'（新的） WHERE name = 'AA'（旧的，原来就有的）;”

mysql> SELECT * FROM tab22;
+----+------+---------+
| id | name | salary  |
+----+------+---------+
|  1 | FF   | 5000.00 |
|  2 | BB   | 5000.00 |
|  3 | CC   | 5000.00 |
+----+------+---------+

修改完成后我们检查tab2会发现有变化
​
#创建触发器3  DELETE 对职工进行删除时 工资表中也要刷新当前职工信息
 

CREATE TRIGGER tab11_delete_trigger
    AFTER DELETE ON tab11
    FOR EACH ROW
    BEGIN
        DELETE FROM tab22 WHERE name = old.name;
    END;

测试用户修改：

DELETE FROM tab11 WHERE name = 'BB';

mysql> SELECT * FROM tab11;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | FF   | f    |   18 |
|  3 | CC   | m    |   18 |
+----+------+------+------+