MySQL-08-笔记

1.触发器的概念

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

触发器类型	OLD的含义	NEW的含义
INSERT 型触发器	无 (因为插入前状态无数据)	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据	NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据	无 (因为删除后状态无数据)

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;
    

3.查看触发器

• 语法及演示

  -- 标准语法
  SHOW TRIGGERS;
  
  -- 查看触发器
  SHOW TRIGGERS;
  

4.删除触发器

• 语法及演示

  -- 标准语法
  DROP TRIGGER 触发器名称;
  
  -- 删除DELETE触发器
  DROP TRIGGER account_delete;
  

5.触发器的总结

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