MySQL触发器的相关简介

最新推荐文章于 2024-06-14 08:00:00 发布

现实如此呀

最新推荐文章于 2024-06-14 08:00:00 发布

阅读量395

点赞数

分类专栏： MySQL 文章标签：触发器数据库 mysql trigger

本文链接：https://blog.csdn.net/weixin_47019016/article/details/111032797

版权

MySQL 专栏收录该内容

24 篇文章 4 订阅

订阅专栏

MySQL触发器

一、触发器简介
- 二、创建触发器
- - 三、修改和删除触发器
  - - 四、触发器示例演示

一、触发器简介

1.触发器简介
触发器是一个特殊的存储过程，不同的是执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被MySQL自动调用。
引发触发器执行的事件一般如下：

(1)增加一条学生记录时，会自动检查年龄是否符合范围要求。

(2)每当删除一条学生信息时，自动删除其成绩表上的对应记录。

(3)每当删除一条数据时，在数据库存档表中保留一个备份副本。

触发程序的优点如下：

(1)触发程序的执行是自动的，当对触发程序相关表的数据做出相应的修改后立即执行。

(2)触发程序可以通过数据库中相关的表层叠修改另外的表。

(3)触发程序可以实施比FOREIGN KEY约束、CHECK约束更为复杂的检查和操作。

触发器与表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性。
2.MySQL支持3种触发器
在MySQL中，只有执行INSERT、UPDATE和DELETE操作时才能激活触发器。
(1)INSERT触发器
在INSERT语句执行之前或之后响应的触发器。
使用INSERT触发器需要注意以下几点：

在INSERT触发器代码内，可引用一个名为NEW（不区分大小写）的虚拟表来访问被插入的行。

在BEFORE INSERT触发器中，NEW中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。

对于AUTO_INCREMENT列，NEW在INSERT执行之前包含的值是0，在INSERT执行之后将包含新的自动生成值。

(2)UPDATE触发器
在UPDATE语句执行之前或之后响应的触发器。
使用UPDATE触发器需要注意以下几点：

在UPDATE触发器代码内，可引用一个名为NEW（不区分大小写）的虚拟表来访问更新的值。

在UPDATE触发器代码内，可引用一个名为OLD（不区分大小写）的虚拟表来访问UPDATE语句执行前的值。

在BEFORE UPDATE触发器中，NEW中的值可能也被更新，即允许更改将要用于UPDATE语句中的值（只要具有对应的操作权限）。

OLD中的值全部是只读的，不能被更新。 

注意：当触发器设计对触发表自身的更新操作时，只能使用BEFORE类型的触发器，AFTER类型的触发器将不被允许。

(3)DELETE触发器
在DELETE语句执行之前或之后响应的触发器。
使用DELETE触发器需要注意以下几点：

在DELETE触发器代码内，可以引用一个名为OLD（不区分大小写）的虚拟表来访问被删除的行。

OLD中的值全部是只读的，不能被更新。

3.总结
触发器使用的过程中，MySQL会按照以下方式来处理错误。

(1)若对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚;

(2)对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效;

(3)若BEFORE触发程序失败，则MySQL将不执行相应行上的操作;

(4)若在BEFORE或AFTER触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

(5)仅当BEFORE触发程序和行操作均已被成功执行，MySQL才会执行AFTER触发程序。

二、创建触发器

触发器是与MySQL数据表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
1.基本语法

在MySQL 5.7中，可以使用CREATE TRIGGER语句创建触发器。

2.语法格式

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

CREATE TRIGGER <触发器名> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名> FOR EACH Row <触发器主体>

3.语法说明
(1)触发器名
触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。
(2)INSERT | UPDATE | DELETE
触发事件，用于指定激活触发器的语句的种类。

INSERT：将新行插入表时激活触发器。
例如，INSERT的BEFORE触发器不仅能被MySQL的INSERT语句激活，也能被LOAD DATA语句激活。

DELETE：从表中删除某一行数据时激活触发器。
例如DELETE和REPLACE语句。

UPDATE：更改表中某一行数据时激活触发器。
例如UPDATE语句。

(3)BEFORE | AFTER
触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。
若希望验证新数据是否满足条件，则使用BEFORE选项；
若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用AFTER选项。
(4)表名
与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个BEFORE UPDATE触发器，但可以有一个BEFORE UPDATE触发器和一个BEFORE INSERT触发器，或一个BEFORE UPDATE触发器和一个AFTER UPDATE触发器。
(5)触发器主体
触发器动作主体，包含触发器激活时将要执行的MySQL语句。如果要执行多个语句，可使
用BEGIN…END复合语句结构。
(6)FOR EACH ROW
一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。
例如，使用INSERT语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。
注意：每个表都支持INSERT、UPDATE和DELETE的BEFORE与AFTER，因此每个表最多支持6个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。
另外，在MySQL中，若需要查看数据库中已有的触发器，则可以使用SHOW TRIGGERS语句。
4.创建BEFORE类型触发器
在test_db数据库中，数据表tb_emp8为员工信息表，包含id、name、deptid和salary字段，数据表tb_emp8的表结构如下所示。

mysql> create table tb_emp8(
    -> id int primary key,
    -> name varchar(22) unique,
    -> deptid int not null,
    -> salary float default 0
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tb_emp8;
Empty set (0.00 sec)

mysql> desc tb_emp8;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(22) | YES  | UNI | NULL    |       |
| deptid | int(11)     | NO   |     | NULL    |       |
| salary | float       | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

实例1
创建一个名为sumofsalary的触发器，触发的条件是向数据表tb_emp8中插入数据之前，对新插入的salary字段值进行求和计算。输入的SQL语句和执行过程如下所示。

mysql> create trigger sumofsalary
    -> before insert on tb_emp8
    -> for each row
    -> set @sum=@sum+new.salary;
Query OK, 0 rows affected (0.00 sec)

触发器sumofsalary创建完成之后，向表tb_emp8中插入记录时，定义的sum值由0变成了1500，即插入值1000和500的和，如下所示。

mysql> set @sum=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_emp8
    -> values(1,'a',1,1000),(2,'b',1,500);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select @sum;
+------+
| @sum |
+------+
| 1500 |
+------+
1 row in set (0.00 sec)

5.创建AFTER类型触发器
在test_db数据库中，数据表tb_emp6和tb_emp7都为员工信息表，包含id、name、deptid和salary字段，数据表tb_emp6和tb_emp7的表结构如下所示。

mysql> create table tb_emp6(
    -> id int primary key,
    -> name varchar(22),
    -> deptid int,
    -> salary float
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create table tb_emp7(
    -> id int primary key,
    -> name varchar(22),
    -> deptid int,
    -> salary float
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(22) | YES  |     | NULL    |       |
| deptid | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc tb_emp7;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(22) | YES  |     | NULL    |       |
| deptid | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

实例2
创建一个名为double_salary的触发器，触发的条件是向数据表tb_emp6中插入数据之后，再向数据表tb_emp7中插入相同的数据，并且salary为tb_emp6中新插入的salary字段值的2倍。输入的SQL语句和执行过程如下所示。

mysql> create trigger double_salary
    -> after insert on tb_emp6
    -> for each row
    -> insert into tb_emp7
    -> values(new.id,new.name,new.deptid,2*new.salary);
Query OK, 0 rows affected (0.00 sec)

触发器double_salary创建完成之后，向表tb_emp6中插入记录时，同时向表tb_emp7中插入相同的记录，并且salary字段为tb_emp6中salary字段值的2倍，如下所示。

mysql> insert into tb_emp6
    -> values(1,'a',1,1000),(2,'b',1,500);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tb_emp6;
+----+------+--------+--------+
| id | name | deptid | salary |
+----+------+--------+--------+
|  1 | a    |      1 |   1000 |
|  2 | b    |      1 |    500 |
+----+------+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from tb_emp7;
+----+------+--------+--------+
| id | name | deptid | salary |
+----+------+--------+--------+
|  1 | a    |      1 |   2000 |
|  2 | b    |      1 |   1000 |
+----+------+--------+--------+
2 rows in set (0.01 sec)

三、修改和删除触发器

修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器。
1.基本语法
与其他MySQL数据库对象一样，可以使用DROP语句将触发器从数据库中删除。
2.语法格式

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

3.语法说明

(1)触发器名
要删除的触发器名称。

(2)数据库名
可选项。指定触发器所在的数据库的名称。若没有指定，则为当前默认的数据库。

(3)权限
执行DROP TRIGGER语句需要SUPER权限。

(4)IF EXISTS
可选项。避免在没有触发器的情况下删除触发器。

注意：删除一个表的同时，也会自动删除该表上的触发器。另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，再重新创建。

4.查看触发器
语法

SHOW CREATE TRIGGER trigger_name
或
SHOW TRIGGERS [FROM db_name] [like_or_where]

示例

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: double_salary
               Event: INSERT
               Table: tb_emp6
           Statement: insert into tb_emp7
values(new.id,new.name,new.deptid,2*new.salary)
              Timing: AFTER
             Created: 2020-12-26 12:06:44.73
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: sumofsalary
               Event: INSERT
               Table: tb_emp8
           Statement: set @sum=@sum+new.salary
              Timing: BEFORE
             Created: 2020-12-26 11:43:46.67
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

5.删除触发器
使用DROP TRIGGER语句可以删除MySQL中已经定义的触发器。
示例
删除double_salary触发器，输入的SQL语句和执行过程如下所示。

mysql> drop trigger double_salary;
Query OK, 0 rows affected (0.00 sec)

删除double_salary触发器后，再次向数据表tb_emp6中插入记录时，数据表tb_emp7的数据不再发生变化，
如下所示。

mysql> insert into tb_emp6
    -> values(3,'c',1,200);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_emp6;
+----+------+--------+--------+
| id | name | deptid | salary |
+----+------+--------+--------+
|  1 | a    |      1 |   1000 |
|  2 | b    |      1 |    500 |
|  3 | c    |      1 |    200 |
+----+------+--------+--------+
3 rows in set (0.00 sec)

mysql> select * from tb_emp7;
+----+------+--------+--------+
| id | name | deptid | salary |
+----+------+--------+--------+
|  1 | a    |      1 |   2000 |
|  2 | b    |      1 |   1000 |
+----+------+--------+--------+
2 rows in set (0.00 sec)

四、触发器示例演示

1.语法格式

DELIMITER ##
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON tbl_name FOR EACH ROW
begin
...
statement
...
end
DELIMITER ;

2.创建goods和orders表

mysql> create table goods
    -> (
    -> g_id int,
    -> name varchar(22),
    -> quantity int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table orders
    -> (
    -> o_id int,
    -> g_id int,
    -> counts int
    -> );
Query OK, 0 rows affected (0.01 sec)

3.向货物表中插入数据

mysql> insert into goods
    -> values(1,'foods',100),(2,'computer',100),(3,'cloth',100);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from goods;
+------+----------+----------+
| g_id | name     | quantity |
+------+----------+----------+
|    1 | foods    |      100 |
|    2 | computer |      100 |
|    3 | cloth    |      100 |
+------+----------+----------+
3 rows in set (0.00 sec)

4.insert触发器效果演示
新旧表区分界限为操作体的操作前后。

delimiter ##
create trigger test1
after insert 
on orders for each row
begin
update goods set quantity=quantity-new.counts where g_id=new.g_id;
end ##
delimiter ;
mysql> select * from goods;
+------+----------+----------+
| g_id | name     | quantity |
+------+----------+----------+
|    1 | foods    |      100 |
|    2 | computer |      100 |
|    3 | cloth    |      100 |
+------+----------+----------+
3 rows in set (0.00 sec)

mysql> insert into orders 
    -> values (1,3,10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from orders;
+------+------+--------+
| o_id | g_id | counts |
+------+------+--------+
|    1 |    3 |     10 |
+------+------+--------+
1 row in set (0.00 sec)

mysql> select * from goods;
+------+----------+----------+
| g_id | name     | quantity |
+------+----------+----------+
|    1 | foods    |      100 |
|    2 | computer |      100 |
|    3 | cloth    |       90 |
+------+----------+----------+
3 rows in set (0.00 sec)

5.delete触发器效果演示

delimiter ##
create trigger test2
after delete 
on orders for each row
begin
update goods set quantity=quantity+old.counts where g_id=old.g_id;
end ##
delimiter ;
mysql> delete from orders where o_id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

mysql> select * from goods;
+------+----------+----------+
| g_id | name     | quantity |
+------+----------+----------+
|    1 | foods    |      100 |
|    2 | computer |      100 |
|    3 | cloth    |      100 |
+------+----------+----------+
3 rows in set (0.00 sec)

6.update触发器效果演示

delimiter ##
create trigger test3
after update 
on orders for each row
begin
update goods set quantity=quantity-(new.counts-old.counts) where g_id=new.g_id;
end ##
delimiter ;

mysql> select * from goods;
+------+----------+----------+
| g_id | name     | quantity |
+------+----------+----------+
|    1 | foods    |      100 |
|    2 | computer |      100 |
|    3 | cloth    |       80 |
+------+----------+----------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+------+------+--------+
| o_id | g_id | counts |
+------+------+--------+
|    1 |    3 |     20 |
+------+------+--------+
1 row in set (0.00 sec)

mysql> update orders set counts=15 where o_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+------+------+--------+
| o_id | g_id | counts |
+------+------+--------+
|    1 |    3 |     15 |
+------+------+--------+
1 row in set (0.00 sec)

mysql> select * from goods;
+------+----------+----------+
| g_id | name     | quantity |
+------+----------+----------+
|    1 | foods    |      100 |
|    2 | computer |      100 |
|    3 | cloth    |       85 |
+------+----------+----------+
3 rows in set (0.00 sec)

现实如此呀

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL触发器的相关简介

MySQL触发器一、MySQL创建触发器（CREATE TRIGGER）二、MySQL修改和删除触发器（DROP TRIGGER）MySQL 数据库中触发器是一个特殊的存储过程，不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被 MySQL自动调用。引发触发器执行的事件一般如下：增加一条学生记录时，会自动检查年龄是否符合范围要求。每当删除一条学生信息时，自动删除其成绩表上的对应记录。每当删除一条数据
复制链接

扫一扫

专栏目录