$7 触发器相关操作

本节记录触发器相关操作

查看触发器

1. SHOW TRIGGERS FROM database

   (root@localhost) [book]> show triggers from book\G
   *************************** 1. row ***************************
                Trigger: t1
                  Event: INSERT
                  Table: o
              Statement: begin if new.much>5 then set new.much=5 ; end if; update g set number=number-new.much where id=new.gid; end
                 Timing: BEFORE
                Created: 2020-02-10 15:19:27.80
               sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                Definer: root@localhost
   character_set_client: utf8mb4
   collation_connection: utf8mb4_general_ci
     Database Collation: gb2312_chinese_ci
   *************************** 2. row ***************************
                Trigger: update_trigger
                  Event: UPDATE
                  Table: student
              Statement: update borrow set borrow.stu_id=new.stu_id
   where borrow.stu_id=old.stu_id
                 Timing: AFTER
                Created: 2020-02-10 14:53:08.39
               sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                Definer: root@localhost
   character_set_client: utf8mb4
   collation_connection: utf8mb4_general_ci
     Database Collation: gb2312_chinese_ci
   *************************** 3. row ***************************
                Trigger: t_trigger
                  Event: INSERT
                  Table: t
              Statement: begin
   set @x='Hello';
   set new.s1=25;
   end
                 Timing: BEFORE
                Created: 2020-02-10 15:04:19.28
               sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                Definer: root@localhost
   character_set_client: utf8mb4
   collation_connection: utf8mb4_general_ci
     Database Collation: gb2312_chinese_ci
   3 rows in set (0.00 sec)

创建触发器

1. CREATE TRIGGER trigger

   (root@localhost) [book]> create trigger update_trigger
       -> after update on student for each row
       -> update borrow_copy set stu_id=new.stu_id         注意NEW关键字
       -> where borrow_copy.stu_id=old.stu_id;             注意OLD关键字
   Query OK, 0 rows affected (0.00 sec)
   
   
   (root@localhost) [book]> create trigger delete_trigger
       -> after delete on student for each row
       -> delete from borrow
       -> where borrow.stu_id=old.stu_id;
   Query OK, 0 rows affected (0.01 sec)

2. 推荐操作

   (root@localhost) [book]> delimiter $$                   设置默认结束符为$$
   (root@localhost) [book]> create trigger t_trigger
       -> before insert on t for each row
       -> begin                                            两行代码及以上要用begin...end包括
       -> set @x='Hello';
       -> set new.s1=25;
       -> end$$
   Query OK, 0 rows affected (0.01 sec)
   
   (root@localhost) [book]> delimiter ;                    再声明回来;
   
   
   测试结果:
   (root@localhost) [book]> insert into t values
       -> (1);
   Query OK, 1 row affected (0.02 sec)
   
   (root@localhost) [book]> select * from t;
   +------+
   | s1   |
   +------+
   |   25 |
   +------+
   1 row in set (0.01 sec)

3. 触发器案例操作

   (root@localhost) [book]> create table g(
       ->id int primary key,
       -> name char(5),
       -> number int);
   Query OK, 0 rows affected (0.02 sec)
   
   (root@localhost) [book]> create table o(
       -> oid int primary key,
       -> gid int,
       -> much int);
   Query OK, 0 rows affected (0.02 sec)
   
   (root@localhost) [book]> insert into g values
       -> (1, '猪', 22),
       -> (2, '羊', 19);
   Query OK, 2 rows affected (0.00 sec)
   Records: 2  Duplicates: 0  Warnings: 0
   
   (root@localhost) [book]> select * from g;
   +----+------+--------+
   | id | name | number |
   +----+------+--------+
   |  1 | 猪   |     22 |
   |  2 | 羊   |     19 |
   +----+------+--------+
   2 rows in set (0.00 sec)
   
   (root@localhost) [book]> delimiter $$
   (root@localhost) [book]> create trigger t1
       -> before insert on o for each row
       -> begin
       -> if new.much>5 then
       -> set new.much=5;
       -> end if;
       -> update g set number=number-new.much where id=new.gid;
       -> end $$
   Query OK, 0 rows affected (0.00 sec)
   
   (root@localhost) [book]> delimiter ;
   
   (root@localhost) [book]> insert into o values
       -> (1,2,10);
   Query OK, 1 row affected (0.03 sec)
   
   (root@localhost) [book]> select * from o;
   +-----+------+------+
   | oid | gid  | much |
   +-----+------+------+
   |   1 |    2 |    5 |
   +-----+------+------+
   1 row in set (0.00 sec)
   
   (root@localhost) [book]> select * from g;
   +----+------+--------+
   | id | name | number |
   +----+------+--------+
   |  1 | 猪   |     22 |
   |  2 | 羊   |     14 |
   +----+------+--------+
   2 rows in set (0.00 sec)

删除触发器

1. DROP TRIGGER trigger

   (root@localhost) [book]> drop trigger delete_trigger;
   Query OK, 0 rows affected (0.01 sec)

修改触发器

1. 先删除,再新建