MySQL触发器

最新推荐文章于 2023-08-24 10:40:03 发布

AFeng521web

最新推荐文章于 2023-08-24 10:40:03 发布

阅读量327

点赞数 3

分类专栏： MySQL学习笔记文章标签： MySQL触发器

本文链接：https://blog.csdn.net/weixin_37972723/article/details/79639013

版权

MySQL学习笔记专栏收录该内容

9 篇文章 0 订阅

订阅专栏

定时炸弹—MySQL触发器

一、触发器概述

触发器是与表有关的数据库对象，在满足指定条件时触发，并执行触发器中定义的语句集合,这样可以令某些操作之间的一致性得到协调。

触发器的特性
1、什么条件触发： insert delete update;
2、什么时候触发：在增删改前（before）或者后（after）;
3、触发频率：针对每一行的执行
4、触发器定义在表上，附着在表上。
也就是由事件来触发某个操作，事件包括insert语句，delete语句和update语句；

注意点：尽量少使用触发器，不建议使用
假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。

触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

二、创建触发器

create trigger 触发器名 before | after 触发事件
on 表名 for each row 执行语句

当为bofore时，表示在增删改之前先执行触发器中定义的代码，然后在执行增删改操作。

1、创建只有一个执行语句的触发器

mysql> create table timelog(
    -> id int(11) primary key auto_increment,
    -> savetime varchar(50) not null
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> create table studentinfo(
    -> id int(11) primary key auto_increment,
    -> name varchar(50) not null
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> create trigger now_time before insert
    -> on studentinfo for each row
    -> insert into timelog(savetime) values(now());
Query OK, 0 rows affected (0.24 sec)

mysql> insert into studentinfo(name) values('AFeng');
Query OK, 1 row affected (0.10 sec)

mysql> select * from timelog;
+----+---------------------+
| id | savetime            |
+----+---------------------+
|  1 | 2018-03-21 12:46:16 |
+----+---------------------+
1 row in set (0.00 sec)

触发器的这种操作相当于on后面所跟的表上定义了一个事件，当该事件发生时，执行所定义的SQL语句。

2、创建具有多个执行语句的触发器

create trigger 触发器名 before | after 触发事件
on 表名 for each row 
begin
  执行语句列表
end

mysql> create table timelog(
    -> id int(11) primary key auto_increment,
    -> now_time varchar(50) not null
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> create table studentinfo(
    -> id int(11) primary key auto_increment,
    -> name varchar(50) not null
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> create table timeinfo(
    -> id int(11) primary key auto_increment,
    -> info varchar(50) not null
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> delimiter //
mysql> create trigger delete_time_info before delete
    -> on studentinfo for each row
    -> begin
    -> insert into timelog(now_time) values(now());
    -> insert into timeinfo(info) values('deleteach');
    -> end
    -> //
Query OK, 0 rows affected (0.20 sec)
mysql> delimiter ;
mysql> insert into studentinfo(name) values('AFeng');
Query OK, 1 row affected (0.08 sec)

mysql> delete from studentinfo where id = 1;
Query OK, 1 row affected (0.10 sec)

mysql> select * from timelog;
+----+---------------------+
| id | now_time            |
+----+---------------------+
|  1 | 2018-03-21 13:34:04 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> select * from timeinfo;
+----+-----------+
| id | info      |
+----+-----------+
|  1 | deleteach |
+----+-----------+
1 row in set (0.00 sec)

三、查看触发器

1、show triggers

mysql> show triggers\G;
*************************** 1. row ***************************
             Trigger: delete_time_info
               Event: DELETE
               Table: studentinfo
           Statement: begin
insert into timelog(now_time) values(now());
insert into timeinfo(info) values('deleteach');
end
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

2、在information_schema.triggers表中查看触发器信息。

mysql> select * from information_schema.triggers
    -> where trigger_name = 'delete_time_info'\G;
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: learnmysql
              TRIGGER_NAME: delete_time_info
        EVENT_MANIPULATION: DELETE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: learnmysql
        EVENT_OBJECT_TABLE: studentinfo
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
insert into timelog(now_time) values(now());
insert into timeinfo(info) values('deleteach');
end
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.06 sec)

四、删除触发器

1、drop trigger [if exists] trigger_name

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

mysql> select * from information_schema.triggers
    -> where trigger_name = 'delete_time_info'\G;
Empty set (0.06 sec)

如果不需要某个触发器时一定要将这个触发器删除，以免造成不必要的麻烦。

五、触发器的执行顺序（before触发器，表操作，after触发器）

mysql> CREATE TRIGGER before_in BEFORE INSERT ON
    -> studentinfo FOR EACH ROW
    -> INSERT INTO timeinfo(info) VALUES("before");
    -> //
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TRIGGER after_in AFTER INSERT ON
    -> studentinfo FOR EACH ROW
    -> INSERT INTO timeinfo(info) VALUES("after");
    -> //
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO studentinfo(name) VALUES("AFeng")//
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM timeinfo//

+----+-----------+
| id | info      |
+----+-----------+
|  1 | deleteact |
|  2 | before    |
|  3 | after     |
+----+-----------+
3 rows in set (0.02 sec)

查询结果表明：before触发器首先被激活，然后才是after触发器被激活。

六、触发器中的new和old

mysql中定义了new和old，用来表示与触发器所关联的表（也是相当于事件源）中触发了触发器的那一行数据，也就是新增加，删除或者更新的那一行数据，来引用触发器中发生变化的记录内容。

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

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

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

mysql> select * from user;
+----+-----+
| id | age |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |  33 |
+----+-----+
3 rows in set (0.00 sec)

mysql> delimiter //

mysql> create trigger young before update on user
    -> for each row
    -> begin
    ->      if new.age < 10 then set new.age = 18;
    ->      elseif new.age > 60 then set new.age = 28;
    ->      end if;
    -> end;//
Query OK, 0 rows affected (0.20 sec)

mysql> update user set age = 8 where id = 1 //
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user//
+----+-----+
| id | age |
+----+-----+
|  1 |  18 |
|  2 |  22 |
|  3 |  33 |
+----+-----+
3 rows in set (0.00 sec)

参考链接

http://www.cnblogs.com/geaozhang/p/6819648.html
https://www.chenyudong.com/archives/database-trigger-new-old-value-understand.html

AFeng521web

关注

3
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
MySQL触发器

定时炸弹—MySQL触发器一、触发器概述触发器是与表有关的数据库对象，在满足指定条件时触发，并执行触发器中定义的语句集合,这样可以令某些操作之间的一致性得到协调。触发器的特性 1、什么条件触发： insert delete update; 2、什么时候触发：在增删改前（before）或者后（after）; 3、触发频率：针对每一行的执行 4、触发器定义在表上，附着在表上...
复制链接

扫一扫