MySQL管理与优化(10)：触发器

最新推荐文章于 2024-07-06 12:03:33 发布

weixin_34279184

最新推荐文章于 2024-07-06 12:03:33 发布

阅读量75

点赞数

文章标签： python 数据库

原文链接：https://my.oschina.net/indestiny/blog/282305

版权

2019独角兽企业重金招聘Python工程师标准>>>

触发器

触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

创建触发器：

创建触发器语法：

CREATE
    [DEFINER = { user | CURRENT_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

触发器只能创建在永久表上，不能对临时表创建触发器。
对同一个表相同触发时间的相同触发事件，只能定义一个触发器。
范例

-- 建表
mysql> CREATE TABLE users (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password VARCHAR(32) NOT NULL,
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE user_profiles(
    -> id INT NOT NULL AUTO_INCREMENT,
    -> user_id INT NOT NULL,
    -> name VARCHAR(10),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)

-- 定义触发器
CREATE TRIGGER user_insert_trigger 
AFTER INSERT ON users FOR EACH ROW  
BEGIN 
INSERT INTO user_profiles VALUES (null, NEW.id, NEW.username); 
END;

-- 插入数据
mysql> INSERT INTO users VALUES (null, 'john', '123456');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM users;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | john     | 123456   |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM user_profiles;
+----+---------+------+
| id | user_id | name |
+----+---------+------+
|  1 |       1 | john |
+----+---------+------+
1 row in set (0.00 sec)

对于有重复记录，需要进行UPDATE操作的INSERT，触发器触发的顺序为：BEFORE INSERT, BEFORE UPDATE, AFTER UPDATE; 对于没有重复记录的INSERT操作，也就是普通INSERT, 触发器触发顺序为：BEFORE INSERT, AFTER INSERT。

删除触发器：

删除触发器语法：

DROP TRIGGER [schema_name.]trigger_name

查看触发器：

可用语法：

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: user_insert_trigger
               Event: INSERT
               Table: users
           Statement: BEGIN INSERT INTO user_profiles VALUES (null, NEW.id, NEW.username); END
              Timing: AFTER
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

-- 通过information_schema.triggers来查找
mysql> SELECT * FROM information_schema.triggers where trigger_name = 'user_insert_trigger'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: user_insert_trigger
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: users
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN INSERT INTO user_profiles VALUES (null, NEW.id, NEW.username); 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: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)