当前位置：首页 > 资讯 > 技术文档

MySQL中触发器的基础学习教程

时间：2021-06-28 04:41 编辑：来源：阅读：
扫一扫，手机访问

摘要：MySQL中触发器的基础学习教程

[b]0.触发器的基本概念 [/b]触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。数据库触发器有以下的作用： (1).安全性。可以基于数据库的值使用户具有操作数据库的某种权利。 # 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。 # 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。 (2).审计。可以跟踪用户对数据库的操作。 # 审计用户操作数据库的语句。 # 把用户对数据库的更新写入审计表。 (3).实现复杂的数据完整性规则 # 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。 # 提供可变的缺省值。 (4).实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。 # 在修改或删除时级联修改或删除其它表中的与之匹配的行。 # 在修改或删除时把其它表中的与之匹配的行设成NULL值。 # 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。 # 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。 (5).同步实时地复制表中的数据。 (6).自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。 [b]1. 创建触发器语法[/b]

CREATE
 [DEFINER = { user | CURRENT_USER }]
 TRIGGER trigger_name
 trigger_time trigger_event
 ON tbl_name FOR EACH ROW
 trigger_body

trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

CREATE
 [DEFINER = { user | CURRENT_USER }]
 TRIGGER trigger_name
 trigger_time trigger_event
 ON tbl_name FOR EACH ROW
 trigger_body
 
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

语法相关部分说明： [b]1.1 授权与回收 [/b]创建触发器需要有CREATE TRIGGER权限：

grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;
grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;

权限收回：

revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;
revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;

[b]1.2 trigger_name [/b]必须给触发器命令，最多64个字符，建议用表的名字_触发器类型的缩写方法命名。如ttlsa_posts_bi(表ttlsa_posts，触发器发生在insert之前before) [b]1.3 DEFINER子句 [/b]在激活触发器时，检查访问权限，确保触发器安全使用。 [b]1.4 trigger_time [/b]定义触发器触发时间。可以设置为在行记录更改之前或之后发生。 [b]1.5 trigger_event [/b]定义触发器触发事件。触发的事件有： [b]1.5.1 [/b]INSERT：当一个新行插入到表中时触发。如INSERT、LOAD DATA和REPLACE语句。 UPDATE：当一个行数据被更改时触发。如UPDATE语句。 DELETE：当一个行从表中删除时触发。如DELETE和REPLACE语句。注意：DROP TABLE和TRUNCATE TABLE语句不会触发该触发器，因为它们不是使用DELETE。同样删除一个分区表也不会触发。有一个潜在的混乱情况，如INSERT INTO ... ON DUPLICATE KEY UPDATE ... 取决于是否有重复键行。不能对一个表创建具有相同的触发事件和触发时间的多个触发器。如对于一个表不能创建两个BEFORE UPDATE触发器，但是，可以创建一个BEFORE UPDATE和一个BEFORE INSERT或一个BEFORE UPDATE和一个AFTER UPDATE触发器。 [b]1.6 FOR EACH ROW子句 [/b]定义触发执行间隔。FOR EACH ROW子句定义触发器每隔一行执行一次动作，而不是对整个表执行一次。 [b]1.7 trigger_body子句 [/b]包含要触发执行的SQL语句。可以是任何合法的语句，包括复合语句(需要使用BEGIN ... END结构)，流控制语句(if、case、while、loop、for、repeat、leave、iterate)，变量声明(declare)以及指派(set)，异常处理声明，允许条件声明，但是这里的语句受的限制和函数的一样。 [b]1.7.1 OLD与NEW [/b]在触发器的SQL语句中，可以关联表中的任何列，通过使用OLD和NEW列名来标识，如OLD.col_name、NEW.col_name。OLD.col_name关联现有的行的一列在被更新或删除前的值。NEW.col_name关联一个新行的插入或更新现有的行的一列的值。对于INSERT语句，只有NEW是合法的。否则会报错：ERROR 1363 (HY000): There is no OLD row in on INSERT trigger 对于DELETE语句，只有OLD是合法的。否则会报错：ERROR 1363 (HY000): There is no NEW row in on DELETE trigger 对于UPDATE语句，NEW和OLD可以同时使用。 [b]2. 实例 2.1 创建表 [/b]使用在《mysqludf_json将关系数据以JSON编码》一文中创建的表。后续会将用户表迁移到nosql数据库上的。

mysql> create table `ttlsa_users` (
  -> `uid` int(11) unsigned,
  -> `username` varchar(40) NOT NULL,
  -> `password` varchar(40) NOT NULL,
  -> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -> PRIMARY KEY (`uid`)
  -> );

mysql> create table `ttlsa_users` (
  -> `uid` int(11) unsigned,
  -> `username` varchar(40) NOT NULL,
  -> `password` varchar(40) NOT NULL,
  -> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -> PRIMARY KEY (`uid`)
  -> );

创建另外一张表来存放触发器动作数据。

mysql> create table `ttlsa_users3` (
  -> `uid` int(11) unsigned,
  -> `userinfo` varchar(200),
  -> );

mysql> create table `ttlsa_users3` (
  -> `uid` int(11) unsigned,
  -> `userinfo` varchar(200),
  -> );

[b]2.2 创建触发器[/b]

mysql> delimiter //
mysql> create trigger ttlsa_users_ai
  -> after insert on ttlsa_users
  -> for each row
  -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));
  -> //

mysql> create trigger ttlsa_users_au
  -> after update on ttlsa_users
  -> for each row
  -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;
  -> //

mysql> delimiter //
mysql> create trigger ttlsa_users_ai
  -> after insert on ttlsa_users
  -> for each row
  -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));
  -> //
 
mysql> create trigger ttlsa_users_au
  -> after update on ttlsa_users
  -> for each row
  -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;
  -> //

[b]2.3 测试[/b]

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username  | password             | createtime     | json_data             |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                |
| 890 | xuhh    | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger            |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+
| userinfo                                  | uid |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

mysql> update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username  | password             | createtime     | json_data             |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                |
| 890 | xuhh    | test_update           | 2013-08-14 16:41:33 | test trigger            |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+
| userinfo                                  | uid |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"test_update"}           | 890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username  | password             | createtime     | json_data             |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                |
| 890 | xuhh    | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger            |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+
| userinfo                                  | uid |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

mysql> update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username  | password             | createtime     | json_data             |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                |
| 890 | xuhh    | test_update           | 2013-08-14 16:41:33 | test trigger            |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+
| userinfo                                  | uid |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"test_update"}           | 890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

[b]3. 管理 3.1 列出触发器[/b]

mysql> SHOW TRIGGERS like '%ttlsa%'; 触发器名称匹配%ttlsa%

*************************** 1. row ***************************
       Trigger: ttlsa_users_ai
        Event: INSERT
        Table: ttlsa_users
      Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
       Timing: AFTER
       Created: NULL
      sql_mode: NO_ENGINE_SUBSTITUTION
       Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
       Trigger: ttlsa_users_au
        Event: UPDATE
        Table: ttlsa_users
      Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
       Timing: AFTER
       Created: NULL
      sql_mode: NO_ENGINE_SUBSTITUTION
       Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)

mysql> SHOW TRIGGERS; #列出所有
mysql> SHOW TRIGGERS from database_name; #列出数据库的触发器
mysql> SHOW CREATE TRIGGER trigger_name;  #查看创建触发器

*************************** 1. row ***************************
        Trigger: ttlsa_users_ai
       sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
 character_set_client: utf8
 collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

[b]3.2 INFORMATION_SCHEMA.TRIGGERS表[/b]

sql> SHOW TRIGGERS like '%ttlsa%'; #触发器名称匹配%ttlsa%

*************************** 1. row ***************************
       Trigger: ttlsa_users_ai
        Event: INSERT
        Table: ttlsa_users
      Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
       Timing: AFTER
       Created: NULL
      sql_mode: NO_ENGINE_SUBSTITUTION
       Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
       Trigger: ttlsa_users_au
        Event: UPDATE
        Table: ttlsa_users
      Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
       Timing: AFTER
       Created: NULL
      sql_mode: NO_ENGINE_SUBSTITUTION
       Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)

mysql> SHOW TRIGGERS; #列出所有
mysql> SHOW TRIGGERS from database_name; #列出数据库的触发器
mysql> SHOW CREATE TRIGGER trigger_name;  #查看创建触发器

*************************** 1. row ***************************
        Trigger: ttlsa_users_ai
       sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
 character_set_client: utf8
 collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'\G

*************************** 1. row ***************************
      TRIGGER_CATALOG: def
      TRIGGER_SCHEMA: test
       TRIGGER_NAME: ttlsa_users_au
    EVENT_MANIPULATION: UPDATE
   EVENT_OBJECT_CATALOG: def
    EVENT_OBJECT_SCHEMA: test
    EVENT_OBJECT_TABLE: ttlsa_users
       ACTION_ORDER: 0
     ACTION_CONDITION: NULL
     ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
    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: NO_ENGINE_SUBSTITUTION
          DEFINER: root@127.0.0.1
   CHARACTER_SET_CLIENT: utf8
   COLLATION_CONNECTION: utf8_general_ci
    DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'\G

*************************** 1. row ***************************
      TRIGGER_CATALOG: def
      TRIGGER_SCHEMA: test
       TRIGGER_NAME: ttlsa_users_au
    EVENT_MANIPULATION: UPDATE
   EVENT_OBJECT_CATALOG: def
    EVENT_OBJECT_SCHEMA: test
    EVENT_OBJECT_TABLE: ttlsa_users
       ACTION_ORDER: 0
     ACTION_CONDITION: NULL
     ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
    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: NO_ENGINE_SUBSTITUTION
          DEFINER: root@127.0.0.1
   CHARACTER_SET_CLIENT: utf8
   COLLATION_CONNECTION: utf8_general_ci
    DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

[b]3.3 删除触发器[/b]

mysql> drop trigger trigger_name;
mysql> drop trigger trigger_name;

全部评论(0)

上一篇：SQL Server 2012 创建定时作业(图文并茂，教你轻松快速创建)
下一篇：SQL语句实现查询并自动创建Missing Index

资讯排行榜
更多>>