← 返回学习路线

SYSTEM

触发器

Java基础触发器system
src/content/notes/system/触发器.md

触发器

基本介绍

触发器是与表有关的数据库对象,在 insert/update/delete 之前或之后触发并执行触发器中定义的 SQL 语句

  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
  • 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的
  • 现在触发器还只支持行级触发,不支持语句级触发
触发器类型 OLD的含义 NEW的含义
INSERT 型触发器 无 (因为插入前状态无数据) NEW 表示将要或者已经新增的数据
UPDATE 型触发器 OLD 表示修改之前的数据 NEW 表示将要或已经修改后的数据
DELETE 型触发器 OLD 表示将要或者已经删除的数据 无 (因为删除后状态无数据)

基本操作

  • 创建触发器

    DELIMITER $
    
    CREATE TRIGGER 触发器名称
    BEFORE|AFTER  INSERT|UPDATE|DELETE
    ON 表名
    [FOR EACH ROW]  -- 行级触发器
    BEGIN
    	触发器要执行的功能;
    END$
    
    DELIMITER ;
  • 查看触发器的状态、语法等信息

    SHOW TRIGGERS;
  • 删除触发器,如果没有指定 schema_name,默认为当前数据库

    DROP TRIGGER [schema_name.]trigger_name;

触发演示

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

  • 数据准备

    -- 创建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 型触发器

    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);
    
    -- 查询日志表
    SELECT * FROM account_log;
    /*
    id	operation	operation_time		operation_id	operation_params
    1	INSERT	   	2021-01-26 19:51:11		3	     插入后{id=3,name=王五money=2000}
    */
  • 创建 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;
    
    -- 查询日志表
    SELECT * FROM account_log;
    /*
    id	operation	operation_time		operation_id	  operation_params
    2	UPDATE	   	2021-01-26 19:58:54		2		 更新前{id=2,name=李四money=1000}
    												 更新后{id=2,name=李四money=200}
    */
  • 创建 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;
    
    -- 查询日志表
    SELECT * FROM account_log;
    /*
    id	operation	operation_time		operation_id	operation_params
    3	DELETE		2021-01-26 20:02:48		3	    删除前{id=3,name=王五money=2000}
    */