RDBMS高级技术之触发器

举报
tea_year 发表于 2024/01/16 22:35:11 2024/01/16
【摘要】  本章学习目标​ 理解触发器​ 熟练掌握触发器操作MySQL5.0.2版本开始支持触发器的功能。触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，本章将详细讲解MySQL的触发器。10.1​ 触发器概述10.1.1​ 触发器的概念及优点前面章节学习了MySQL的存储过程，MySQL中还有一种类似的存在...

本章学习目标

​ 理解触发器

​ 熟练掌握触发器操作

MySQL5.0.2版本开始支持触发器的功能。触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，本章将详细讲解MySQL的触发器。

10.1​ 触发器概述

10.1.1​ 触发器的概念及优点

前面章节学习了MySQL的存储过程，MySQL中还有一种类似的存在——触发器，它的执行不是由程序调用，也不是手动开启，而是由事件来触发。当对某个表操作时，会自动激活触发器并执行，例如对一个表进行操作（insert、delete、update）时会激活并执行触发器。

触发器是用户定义在关系表上的一类由事件触发的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器。触发器类似于约束，但是比约束灵活，具有更强大的数据控制能力。

触发器的优点如下所示。

​ 自动执行：触发器在操作表数据后立即被激活。

​ 级联更新：触发器可以通过数据库中的相关表进行层叠更改。

​ 强化约束：触发器可以引用其他表中的列，能够实现比CHECK约束更为复杂的约束。

​ 跟踪变化：触发器可以阻止数据库中未经许可的指定更新和变化。

​ 强制业务逻辑：触发器可用于执行管理任务，并强制影响数据库的复杂业务规则。

10.1.2​ 触发器的作用

触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过多的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

触发器主要有六个作用，具体如下所示。

​ 安全性：可以基于数据库使用户具有操作数据库的某种权利，可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据，还可以基于数据库中的数据限制用户的操作，例如不允许某个用户做修改操作。

​ 审计：可以跟踪用户对数据库的操作，审计用户操作数据库的语句，把用户对数据库的更新写入审计表。

​ 实现复杂的数据完整性规则：实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象，还可以提供可变的缺省值。

​ 实现复杂的非标准的数据库相关完整性规则：触发器可以对数据库中相关的表进行连环更新。

​ 同步实时地复制表中的数据。

​ 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

10.2​ 触发器操作

上节内容详细阐述了触发器的基本概念，接下来将讲解触发器的操作，包括创建触发器、查看触发器、使用触发器和删除触发器。

10.2.1​ 数据准备

在讲解触发器前，首先创建两张数据表，用于后面的例题演示，分别为测试表test1和测试表test2，首先来了解一下表结构，测试表test1的表结构如表10.1所示。

表10.1​ test1表

字段

字段类型

说明

id

int

编号

name

varchar(50)

姓名

表10.1中列出了测试表test1的字段、字段类型和说明，接着创建测试表test1，SQL语句如下所示。

mysql> CREATE TABLE test1(

-> id INT,

-> name VARCHAR(50)

-> );

Query OK, 0 rows affected (0.16 sec)

接着创建测试表test2，表结构如表10.2所示。

表10.2​ test2表

字段

字段类型

说明

id

int

编号

name

varchar(50)

姓名

表10.2中列出了测试表test2的字段、字段类型和说明，接着创建测试表test2，SQL语句如下所示。

mysql> CREATE TABLE test2(
-> id INT,
-> name VARCHAR(50)
-> );

Query OK, 0 rows affected (0.16 sec)

至此，两张表创建完成，本章后面的演示例题会用到这两张表。

10.2.2​ 创建触发器

MySQL中创建触发器的的语法格式如下所示。

CREATE TRIGGER trigger_name
   trigger_time
  trigger_event ON tbl_name
FOR EACH ROW

trigger_stmt

以上语法格式中，trigger_name表示触发器名称，用户自行指定；trigger_time表示触发时机，取值为BEFORE或AFTER；trigger_event表示触发事件，取值为 INSERT、UPDATE或DELETE；tbl_name表示建立触发器的表名，即在哪张表上建立触发器；trigger_stmt表示触发器程序体，可以是一条SQL语句，也可以是BEGIN和END包含的多条语句。由此可以看出，一共可以创建六种触发器，即BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE和AFTER DELETE。另外还要注意，不能同时在一个表上建立两个相同类型的触发器，因此在一个表上最多建立六个触发器。

MySQL除了对INSERT、UPDATE、DELETE基本操作进行定义外，还定义了LOAD DATA和REPLACE语句，这两种语句也能引起上述六种类型触发器的触发。LOAD DATA语句用于将一个文件装入到一个数据表中，相当于一系列的INSERT操作。REPLACE 语句与INSERT语句很像，只是在表中有primary key或unique索引时，若插入的数据和原来 primary key或unique索引一致时，会先删除原来的数据，然后增加一条新数据，可以理解为一条REPLACE语句有时候等价于一条INSERT语句，有时候等价于一条DELETE语句加上一条INSERT语句。关于各种触发器的激活和触发时机具体如下所示。

​ INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA或REPLACE语句触发；

​ UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；

​ DELETE型触发器：删除某一行时激活触发器，可能通过DELETE和REPLACE语句触发。

学习了创建触发器语法格式后，接下来通过具体案例演示INSERT型触发器的创建和使用。

例10-1​ 创建触发器t_afterinsert_on_test1，用于向测试表test1添加记录后，自动将记录备份到测试表test2中，SQL语句如下所示。

mysql> delimiter //
mysql> CREATE TRIGGER t_afterinsert_on_test1
-> AFTER INSERT ON test1
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO test2(id,name) values(NEW.id,NEW.name);
-> END//

Query OK, 0 rows affected (0.27 sec)

以上执行结果证明触发器创建完成，接下来测试触发器的使用，首先向测试表test1中插入一条数据，SQL语句如下所示。

mysql> delimiter ;

mysql> INSERT INTO test1(id,name) values(1,'zs');

Query OK, 1 row affected (0.24 sec)

以上执行结果证明数据插入完成，可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM test1;

+------+------+

| id | name |

+------+------+

| 1 | zs |

+------+------+

1 row in set (0.03 sec)

以上执行结果可以看出数据插入完成，此时查看测试表test2中的数据，SQL语句如下所示。

mysql> SELECT * FROM test2;

+------+------+
| id | name |
+------+------+

| 1 | zs |

+------+------+

1 row in set (0.02 sec)

从以上执行结果可看出，测试表test2自动备份了向测试表test1中插入的数据，这是因为在进行INSERT操作时，激活了触发器t_afterinsert_on_test1，触发器自动向测试表test2中插入了同样的数据。

上述示例中使用了NEW关键字，和SQL Server中的INSERTED和DELETED类似，MySQL中定义了NEW和OLD，用来表示触发器的所在表中，触发了触发器的哪一行数据，NEW和OLD的具体用法如下所示。

​ 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据。

​ 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据。

​ 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据。

NEW关键字的使用语法格式如下所示。

NEW.columnName

以上语法格式中，columnName表示相应数据表的某个列名，OLD关键字也是类似的使用方法，另外需要注意的是，OLD是只读的，而NEW则可以在触发器中使用SET赋值，这样不会再次触发触发器，造成循环调用。

接下来再通过一个案例演示DELETE型触发器的使用。

例10-2​ 创建触发器t_afterdelete_on_test1，用于删除测试表test1记录后，自动将测试表test2中的对应记录删除，SQL语句如下所示。

mysql> delimiter //

mysql> CREATE TRIGGER t_afterdelete_on_test1

-> AFTER DELETE ON test1
-> FOR EACH ROW
-> BEGIN
-> DELETE FROM test2 WHERE id=OLD.id;
-> END//

Query OK, 0 rows affected (0.18 sec)

以上执行结果证明触发器创建完成，接下来测试触发器的使用，首先删除测试表test1中id为1的数据，SQL语句如下所示。

mysql> delimiter ;

mysql> DELETE FROM test1 WHERE id=1;

Query OK, 1 row affected (0.16 sec)

以上执行结果证明数据删除完成，可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM test1;

Empty set (0.00 sec)

以上执行结果可以看出数据删除完成，此时查看测试表test2中的数据，SQL语句如下所示。

mysql> SELECT * FROM test2;

Empty set (0.00 sec)

从以上执行结果可看出，测试表2中的记录同样被删除，这是因为在进行DELETE操作时，激活了触发器t_afterdelete_on_test1，触发器自动删除了测试表test2中对应的记录。

10.2.3​ 查看触发器

查看触发器有两种方式，接下来针对两种方式分别讲解。

1.使用SHOW TRIGGERS语句查看触发器

查询触发器，可以使用SHOW TRIGGERS语句，具体语法格式如下。

SHOW TRIGGERS\G

接下来通过具体案例演示查看触发器。

例10-3​ 使用SHOW TRIGGERS语句查看所有触发器，SQL语句如下所示。

mysql> SHOW TRIGGERS\G

*************************** 1. row ***************************

Trigger: t_afterinsert_on_test1

Event: INSERT

Table: test1

Statement: BEGIN

INSERT INTO test2(id,name) values(new.id,new.name);

END

Timing: AFTER

Created: NULL

sql_mode:

Definer: root@localhost

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: utf8_general_ci

*************************** 2. row ***************************

Trigger: t_afterdelete_on_test1

Event: DELETE

Table: test1

Statement: BEGIN

DELETE FROM test2 WHERE id=OLD.id;

END

Timing: AFTER

Created: NULL

sql_mode:

Definer: root@localhost

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: utf8_general_ci

2 rows in set (0.03 sec)

以上执行结果可看到数据库中有两个触发器，通过SHOW TRIGGERS语句可以查看到两个触发器的Event、Table、Statement等。

2.从information_schema.triggers表中查看触发器

在MySQL中，触发器的信息存储在information_schema库下的triggers表中，可以通过查询该表的数据来查询触发器的信息，并且可以查询指定触发器的指定信息，操作更方便，接下来通过具体案例演示查看triggers表中触发器的信息，首先查看triggers的表结构，SQL语句如下所示。

mysql> DESC information_schema.triggers;

+--------------------------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------------------+---------------+------+-----+---------+-------+

| TRIGGER_CATALOG | varchar(512) | NO | | | |

| TRIGGER_SCHEMA | varchar(64) | NO | | | |

| TRIGGER_NAME | varchar(64) | NO | | | |

| EVENT_MANIPULATION | varchar(6) | NO | | | |

| EVENT_OBJECT_CATALOG | varchar(512) | NO | | | |

| EVENT_OBJECT_SCHEMA | varchar(64) | NO | | | |

| EVENT_OBJECT_TABLE | varchar(64) | NO | | | |

| ACTION_ORDER | bigint(4) | NO | | 0 | |

| ACTION_CONDITION | longtext | YES | | NULL | |

| ACTION_STATEMENT | longtext | NO | | NULL | |

| ACTION_ORIENTATION | varchar(9) | NO | | | |

| ACTION_TIMING | varchar(6) | NO | | | |

| ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES | | NULL | |

| ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES | | NULL | |

| ACTION_REFERENCE_OLD_ROW | varchar(3) | NO | | | |

| ACTION_REFERENCE_NEW_ROW | varchar(3) | NO | | | |

| CREATED | datetime | YES | | NULL | |

| SQL_MODE | varchar(8192) | NO | | | |

| DEFINER | varchar(77) | NO | | | |

| CHARACTER_SET_CLIENT | varchar(32) | NO | | | |

| COLLATION_CONNECTION | varchar(32) | NO | | | |

| DATABASE_COLLATION | varchar(32) | NO | | | |

+--------------------------+---------------+------+-----+---------+-------+

22 rows in set (0.01 sec)

以上执行结果可以看到triggers的表结构，包含Field、Type等信息，接下来通过具体案例演示通过information_schema.triggers表查看触发器的信息。

例10-4​ 通过information_schema.triggers表查看触发器t_afterdelete_on_test1的信息，SQL语句如下所示。

mysql> SELECT * FROM information_schema.triggers

-> WHERE trigger_name='t_afterdelete_on_test1'\G

*************************** 1. row ***************************

TRIGGER_CATALOG: def

TRIGGER_SCHEMA: qianfeng6

TRIGGER_NAME: t_afterdelete_on_test1

EVENT_MANIPULATION: DELETE

EVENT_OBJECT_CATALOG: def

EVENT_OBJECT_SCHEMA: qianfeng6

EVENT_OBJECT_TABLE: test1

ACTION_ORDER: 0

ACTION_CONDITION: NULL

ACTION_STATEMENT: BEGIN

DELETE FROM test2 WHERE id=OLD.id;

END

ACTION_ORIENTATION: ROW

ACTION_TIMING: AFTER

ACTION_REFERENCE_OLD_TABLE: NULL

ACTION_REFERENCE_NEW_TABLE: NULL

ACTION_REFERENCE_OLD_ROW: OLD

ACTION_REFERENCE_NEW_ROW: NEW

CREATED: NULL

SQL_MODE:

DEFINER: root@localhost

CHARACTER_SET_CLIENT: gbk

COLLATION_CONNECTION: gbk_chinese_ci

DATABASE_COLLATION: utf8_general_ci

1 row in set (0.06 sec)

从以上执行结果可看出，通过information_schema.triggers表查看到了触发器t_afterdelete_on_test1的详细信息，包括TRIGGER_SCHEMA、TRIGGER_NAME和EVENT_MANIPULATION等。

10.2.4​ 触发器使用的注意事项

触发器执行的语句有两个限制，具体如下所示。

​ 触发器程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储过程通过参数将数据返回触发程序。也就是存储过程通过OUT或INOUT类型的参数可以将数据返回触发器，但不能调用直接返回数据的过程。

​ 不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如START TRANS-ACTION、COMMIT或ROLLBACK。

​ MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作。如果对事务表进行的操作，如果出现错误，那么将会被回滚，如果是对非事务表进行操作，那么就无法回滚了，数据可能会出错。

10.2.5​ 删除触发器

在删除触发器时，当前用户必须具有删除触发器的权限。此时登录的是root用户，具体删除触发器的权限，这里就不再验证，删除触发器的语法格式如下所示。

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

以上语法格式中，trigger_name是要删除的触发器名称，IF EXISTS是可选的，表示如果触发器不存在，不发生错误，而是产生一个警告，接下来通过具体案例演示删除触发器。

例10-5​ 将触发器t_afterdelete_on_test1删除，SQL语句如下所示。

mysql> DROP TRIGGER qianfeng6.t_afterdelete_on_test1;

Query OK, 0 rows affected (0.14 sec)

以上执行结果证明存储过程删除成功，可以使用SHOW TRIGGERS语句查看数据库中所有的触发器，SQL语句如下所示。

mysql> SHOW TRIGGERS\G

*************************** 1. row ***************************

Trigger: t_afterinsert_on_test1
Event: INSERT

Table: test1
Statement: BEGIN
INSERT INTO test2(id,name) values(new.id,new.name);

END
Timing: AFTER
Created: NULL
sql_mode:
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: utf8_general_ci

1 row in set (0.01 sec)

从以上执行结果可看出，数据库中此时只有一个触发器t_afterinsert_on_test1，通过DROP TRIGGER语句，成功删除了触发器t_afterdelete_on_test1。

10.3​ 小案例

前面小节详细讲解了MySQL中触发器的使用，接下来通过一个小案例，演示使用触发器，更方便地实现数据完整性约束。

案例中有两张表，它们通过外键关联，当删除主表中的记录时，从表中对应的记录就没有意义了，利用触发器，自动将没有意义的数据删除，实现数据的完整性约束。

做案例之前，需要创建两张关联表，学生表student和交换生表bor_student，两张表之间通过外键stu_id关联，首先了解学生表student的表结构，如表10.3所示。

表10.3​ student表

字段

字段类型

说明

stu_id

int

学生编号

stu_name

varchar(30)

学生姓名

stu_sex

enum(‘m’,’f’)

学生性别

表10.3中列出了student表的字段、字段类型和说明，接着创建student表，SQL语句如下所示。

mysql> CREATE TABLE student(
-> stu_id INT NOT NULL PRIMARY KEY,
-> stu_name VARCHAR(30) NOT NULL,
-> stu_sex enum('m','f') DEFAULT 'm'
-> );

Query OK, 0 rows affected (0.16 sec)

创建完成student表后，向表中插入数据，SQL语句如下所示。

mysql> INSERT INTO student
-> VALUES
-> (1,'zs','m'),
-> (2,'ls','f'),
-> (3,'ww','m');

Query OK, 3 rows affected (0.07 sec)

Records: 3 Duplicates: 0 Warnings: 0

以上执行结果证明数据插入完成，可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM student;

+--------+----------+---------+
| stu_id | stu_name | stu_sex |
+--------+----------+---------+
| 1 | zs | m |
| 2 | ls | f |
| 3 | ww | m |

+--------+----------+---------+

3 rows in set (0.00 sec)

接着创建交换生表bor_student，表结构如表10.4所示。

表10.4​ bor_student表

字段

字段类型

说明

bor_id

int

交换编号

stu_id

int

学生编号

bor_date

date

交换日期

ret_date

date

返回日期

表10.4中列出了bor_student表的字段、字段类型和说明，接着创建bor_student表，SQL语句如下所示。

mysql> CREATE TABLE bor_student(
-> bor_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> stu_id INT NOT NULL,
-> bor_date DATE,
-> ret_date DATE,
-> FOREIGN KEY(stu_id) REFERENCES student(stu_id)

-> );

Query OK, 0 rows affected (0.08 sec)

创建完成bor_student表后，向表中插入数据，SQL语句如下所示。

mysql> INSERT INTO bor_student

-> VALUES
-> (1001,1,'2017-01-01','2017-01-20'),
-> (1002,2,'2017-02-02','2017-03-01'),
-> (1003,3,'2017-08-11','2017-10-21');

Query OK, 3 rows affected (0.11 sec)

Records: 3 Duplicates: 0 Warnings: 0

以上执行结果证明数据插入完成，可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM bor_student;

+--------+--------+------------+------------+
| bor_id | stu_id | bor_date | ret_date |
+--------+--------+------------+------------+

| 1001 | 1 | 2017-01-01 | 2017-01-20 |

| 1002 | 2 | 2017-02-02 | 2017-03-01 |

| 1003 | 3 | 2017-08-11 | 2017-10-21 |

+--------+--------+------------+------------+

3 rows in set (0.00 sec)

接下来编写触发器t_beforedelete，当student表中记录删除时，自动删除bor_student表中对应的数据，SQL语句如下所示。

mysql> delimiter //

mysql> CREATE TRIGGER t_beforedelete

-> BEFORE DELETE ON student FOR EACH ROW

-> BEGIN
-> DELETE FROM bor_student
-> WHERE bor_student.stu_id=OLD.stu_id;
-> END//

Query OK, 0 rows affected (0.08 sec)

此时删除学生表中stu_id为3的记录，SQL语句如下所示。

mysql> DELETE FROM student
-> WHERE stu_id=3;

Query OK, 1 row affected (0.04 sec)

以上执行结果证明表student中stu_id为3的记录成功删除，可以查看表中数据进行验证，SQL语句如下所示。

mysql> SELECT * FROM bor_student;

+--------+--------+------------+------------+
| bor_id | stu_id | bor_date | ret_date |
+--------+--------+------------+------------+
| 1001 | 1 | 2017-01-01 | 2017-01-20 |
| 1002 | 2 | 2017-02-02 | 2017-03-01 |
+--------+--------+------------+------------+

2 rows in set (0.00 sec)

表student中的记录删除后，查看表bor_student中对应的记录是否存在，SQL语句如下所示。

mysql> SELECT * FROM bor_student;

+--------+--------+------------+------------+
| bor_id | stu_id | bor_date | ret_date |
+--------+--------+------------+------------+
| 1001 | 1 | 2017-01-01 | 2017-01-20 |
| 1002 | 2 | 2017-02-02 | 2017-03-01 |

+--------+--------+------------+------------+

2 rows in set (0.00 sec)

从以上执行结果可看出，表bor_student中stu_id为3的记录被自动删除，这就是触发器在自动维护数据完整性。

10.4​ 本章小结

本章介绍了触发器的相关内容，首先讲解了触发器的概念，接着讲解了触发器的相关操作，包括触发器的创建、查看、使用和删除等，需要大家通过实践去熟练掌握。

10.5​ 习题

1.思考题

(1) 请简述什么是触发器。

(2) 请简述触发器的优点。

(3) 请简述触发器的作用。

(4) 请简述创建触发器的语法格式。

(5) 请简述删除触发器的语法格式。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
SQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入