DBA MySQL触发器

功能概述

​ 触发器可以定制用户对某张数据表进行增删改操作前以及操作后的行为。

​ 触发器无法主动执行，必须由用户对特定数据表进行增删改操作后被动触发。

​ 触发器无法对查询前或查询后的行为做定制。

创建触发器

​ 创建触发器的语法格式如下：

# delimiter是指自定义结束符，mysql中以分号结束，使用自定义结束符后则以自定义结束符为准
# 自定义结束符的意义在于有可能定制行为中的语句会使用分号，但是此时触发器并未创建完成
# 每个触发器创建完成之后都有一个$，代表该语句以正式结束，触发器以创立

delimiter $

# 插入前

CREATE TRIGGER 触发器名称
    BEFORE INSERT ON 表名
        FOR EACH ROW						  -- 每一行
        BEGIN
            定制行为
        END$

# 插入后
CREATE TRIGGER 触发器名称
    AFTER INSERT ON 表名
        FOR EACH ROW
        BEGIN
            定制行为
        END$

# 更新后
CREATE TRIGGER 触发器名称
    AFTER UPDATE ON 表名
        FOR EACH ROW
        BEGIN
            定制行为
        END$

# 更新前
CREATE TRIGGER 触发器名称
    BEFORE UPDATE ON 表名
        FOR EACH ROW
        BEGIN
            定制行为
        END$

# 删除前
CREATE TRIGGER 触发器名称
    BEFORE DELETE ON 表名
        FOR EACH ROW
        BEGIN
            定制行为
        END$

# 删除后
CREATE TRIGGER 触发器名称
    AFTER DELETE ON 表名
        FOR EACH ROW
        BEGIN
            定制行为
        END$

delimiter ;

删除触发器

​ 删除触发器的语法格式如下：

DROP TRIGGER 触发器名称;

示例演示

​ 以下示例将展示当对userInfo表进行插入与删除记录时，将会向log表中插入一条记录。

NEW代表即将插入的数据行

OLD代表即将被删除的数据行

# 用户表
CREATE TABLE userInfo(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    name CHAR(32) NOT NULL COMMENT "用户名称",
    age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "用户年龄",
    gender ENUM("MALE","FEMALE","UNKNOW") NOT NULL DEFAULT "MALE" COMMENT "用户性别",
    role ENUM("USER","ADMIN") NOT NULL DEFAULT "USER" COMMENT "用户角色"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 日志表
CREATE TABLE log(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    username CHAR(32) NOT NULL COMMENT "用户名称",
    message CHAR(128) NOT NULL COMMENT "日志信息"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建触发器
delimiter $

# 创建触发器,插入之后
  CREATE TRIGGER user_after_insert AFTER INSERT ON userInfo FOR EACH ROW  -- EACH ROW 代表每一行
    BEGIN
      IF NEW.role = "user" THEN -- 如果插入的角色是普通用户  NEW代表即将插入的行
        INSERT INTO log(username,message) VALUES
          (NEW.name,"新增一位普通用户");
      ELSE
        INSERT INTO log(username,message) VALUES
          (NEW.name,"新增一位管理员用户");
      END IF; -- 结束必须加分号
    END $

# 创建触发器，删除之后
  CREATE TRIGGER user_after_delete AFTER DELETE ON userInfo FOR EACH ROW
    BEGIN
      IF OLD.role = "user" THEN -- 如果删除的角色是普通用户 OLD代表即将删除的行
        INSERT INTO log(username,message) VALUES
          (OLD.name,"删除一位普通用户");
      ELSE
        NSERT INTO log(username,message) VALUES
          (OLD.name,"删除一位管理员用户");
      END IF;
    END $
delimiter ;

​ 对userInfo表做删除和插入的操作，查看log表的变化：

# 操作userInfo表
INSERT INTO
    userInfo(name, age, gender, role)
VALUES
    ("YunYa", 18, "male", "admin"),
    ("Jack", 18, "male", "user");

DELETE FROM
    userInfo
WHERE
    id = 1;
   
# 查看log表变化
M > SELECT * FROM LOG;
+----+----------+-----------------------------+
| id | username | message                     |
+----+----------+-----------------------------+
|  1 | YunYa    | 新增一位管理员用户          |
|  2 | Jack     | 新增一位普通用户            |
|  3 | YunYa    | 删除一位管理员用户          |
+----+----------+-----------------------------+