9 MySQL触发器

9.1 触发器的概念

• 触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。
• 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

9.2 创建触发器

• 标准语法

DELIMITER $

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
    触发器要执行的功能;
END$

DELIMITER ;

• 触发器演示。通过触发器记录账户表的数据变更日志。包含：增加、修改、删除

  • 创建账户表

  -- 创建db9数据库
  CREATE DATABASE db9;
  
  -- 使用db9数据库
  USE db9;
  
  -- 创建账户表account
  CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 账户id
    NAME VARCHAR(20),                   -- 姓名
    money DOUBLE                        -- 余额
  );
  -- 添加数据
  INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',2000);
  

  • 创建日志表

  -- 创建日志表account_log
  CREATE TABLE account_log(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 日志id
    operation VARCHAR(20),              -- 操作类型 (insert update delete)
    operation_time DATETIME,            -- 操作时间
    operation_id INT,                   -- 操作表的id
    operation_params VARCHAR(200)       -- 操作参数
  );
  

  • 创建INSERT触发器

  -- 创建INSERT触发器
  DELIMITER $
  
  CREATE TRIGGER account_insert
  AFTER INSERT
  ON account
  FOR EACH ROW
  BEGIN
    INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
  END$
  
  DELIMITER ;
  
  -- 向account表添加记录
  INSERT INTO account VALUES (NULL,'王五',3000);
  
  -- 查询account表
  SELECT * FROM account;
  
  -- 查询日志表
  SELECT * FROM account_log;
  

  • 创建UPDATE触发器

  -- 创建UPDATE触发器
  DELIMITER $
  
  CREATE TRIGGER account_update
  AFTER UPDATE
  ON account
  FOR EACH ROW
  BEGIN
    INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}','修改后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
  END$
  
  DELIMITER ;
  
  -- 修改account表
  UPDATE account SET money=3500 WHERE id=3;
  
  -- 查询account表
  SELECT * FROM account;
  
  -- 查询日志表
  SELECT * FROM account_log;
  

  • 创建DELETE触发器

  -- 创建DELETE触发器
  DELIMITER $
  
  CREATE TRIGGER account_delete
  AFTER DELETE
  ON account
  FOR EACH ROW
  BEGIN
    INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
  END$
  
  DELIMITER ;
  
  -- 删除account表数据
  DELETE FROM account WHERE id=3;
  
  -- 查询account表
  SELECT * FROM account;
  
  -- 查询日志表
  SELECT * FROM account_log;
  

9.3 查看触发器

-- 标准语法
SHOW TRIGGERS;

-- 查看触发器
SHOW TRIGGERS;

9.4 删除触发器

-- 标准语法
DROP TRIGGER 触发器名称;

-- 删除DELETE触发器
DROP TRIGGER account_delete;

9.5 触发器的总结

• 触发器是与表有关的数据库对象
• 可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句
• 触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
• 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容