MySQL之触发器详解加实战触发器概述触发器的操作

90 阅读 0 评论 60 点赞

我是靠谱客的博主丰富发卡，最近开发中收集的这篇文章主要介绍MySQL之触发器详解加实战触发器概述触发器的操作，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

触发器概述

触发器的执行不是由程序调用，也不是手动开启，而是由时间来触发。当对某个表进行操作时会自动激活并执行触发器，例如对一个表进行INSERT、DELETE、UPDATE等操作时会激活并执行触发器。
触发器类似于约束，但是比约束灵活，具有更强的数据控制能力。
触发器的优点如下：
1、自动执行：触发器在操作数据时立即被激活。
2、级联更新：触发器可以通过数据库中的相关表进行层叠更改。
3、强化约束：触发器可以引用其他表中的列，能够实现比CHECK约束更复杂的约束。
4、跟踪变化：触发器可以阻止数据库中未经许可的指定更新和变化。
5、强制业务逻辑：触发器可用于执行管理任务，并强制影响数据库的复杂业务规划。
触发器的作用
触发器是一种特殊的存储过程，它在插入、删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细、更复杂的数据控制能力。触发器主要有6个作用，具体如下。
1、安全性：可以基于数据库使用户具有操作数据库的某种权利，可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库中的数据，还可以基于数据库中的数据限制用户的操作，例如不允许某个用户做修改操作。
2、审计：可以跟踪用户对数据库的操作，审计用户操作数据库的语句，把用户对数据库的更新写入审计表。
3、实现复杂的数据完整性规则，实现非标准的数据完整性检查和约束：触发器可以产生比规则更复杂的限制。与规则不同，触发器可以引用列或数据库对象，还可以提供可变的默认值。
4、实现复杂的非标准的数据库相关完整性规则：触发器可以对数据库中的相关表进行连环更新。
5、同步实时地复制表中的数据。
6、自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

触发器的操作

数据准备

CREATE TABLE test1(id INT,name VARCHAR(50));

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

创建触发器

语法

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包含的多条语句。即BEFORT INSERT、BEFORT UPDATE、BEFORT DELETE、AFTER INSERT 、AFTER UPDATE、AFTER DELETE。此外用户还需注意，不能同时在一个表上建立两个形同类型的触发器，因此在一个表上最多可以建立6个触发器。

mysql除了对INSERT、UPDATE、DELETE等基本操作进行定义外，还定义了LOAD DATA和REPLACE语句，这两种语句也能引起上述6种类型触发器的触发。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语句触发。

1、 INSERT触发器的使用示例。

创建触发器t_afterinsert_on_test1，用于向测试表test1添加记录后自动将记录备份到测试表test2中。

DELIMITER //

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 //

在上面的语句中使用了NEW关键字，在MySQL中定义了NEW和OLD来表示触发器的所在表中触发了触发器的哪一行数据，NEW和OLD的具体用法如下。
在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据。
在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据。
在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据。
NEW关键字的使用语法如下。

NEW.columnName

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

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

SELECT * FROM test1;
+------+------+
| id   | name |
+------+------+
|    1 | zs   |
+------+------+
1 row in set (0.00 sec)

SELECT * FROM test2;
+------+------+
| id   | name |
+------+------+
|    1 | zs   |
+------+------+
1 row in set (0.00 sec)

2、DELETE型触发器的使用示例。

创建触发器t_afterdelete_on_test1，用于删除测试表test1记录后自动将测试表test2中对应的记录删除。

DELIMITER //
CREATE TRIGGER t_afterdelete_on_test1 AFTER DELETE ON test1 FOR EACH ROW BEGIN DELETE FROM test2 WHERE id=OLD.id;END //

测试

DELIMITER ;
DELETE FROM test1 WHERE id=1;

SELECT * FROM test1;
Empty set (0.00 sec)

SELECT * FROM test2;
Empty set (0.00 sec)

查看触发器

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

语法
SHOW TRIGGERSG

SHOW TRIGGERSG
*************************** 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: utf8
collation_connection: utf8_general_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: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.01 sec)

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

SELECT * FROM information_schema.triggersG
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: gws_cfg
              TRIGGER_NAME: t_afterinsert_on_test1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: gws_cfg
        EVENT_OBJECT_TABLE: test1
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN INSERT INTO test2(id,name) values(NEW.id,NEW.name);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: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
*************************** 2. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: gws_cfg
              TRIGGER_NAME: t_afterdelete_on_test1
        EVENT_MANIPULATION: DELETE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: gws_cfg
        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: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
2 rows in set (0.01 sec)

注意：加上;结束符会在输出最后出现ERROR: No query specified错误。

使用触发器

使用触发器的注意事项：
1、触发器程序不能调用将数据返回客户端的存储程序，也不能使用CALL语句等动态SQL语句，但是允许存储过程通过参数将数据返回触发器程序。也就是存储过程通过OUT或INOUT类型的参数可以将数据返回触发器，但不能调用直接返回数据过程。
2、不能在触发器中使用以显示或隐式方式开始或结束事务的语句，例如START TRANSACTION、COMMIT或ROLLBACK。
3、MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作。如果是对事务表进行操作，若出现错误，那么将会被回滚；如果是对非事务表进行操作，那么就无法回滚，数据可能会出错。

删除触发器

语法

DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name

将触发器t_afterdelete_on_test1删除。

DROP TRIGGER t_afterdelete_on_test1;
Query OK, 0 rows affected (0.03 sec)

SHOW TRIGGERSG
*************************** 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: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)