（八）MySQL学习笔记——触发器

一、触发器的概念

• 触发器可以在INSERT、UPDATE、DELETE之前或之后触发定义好的SQL语句。例如对某张表执行INSERT操作后自动触发日志记录的功能，记录到另一张表中。
• 使用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 ;

• 数据准备：两张表，一张是银行账户信息表，一张是日志记录表。

CREATE DATABASE db9;
USE db9;

-- 创建账户表account
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    money DOUBLE
);
-- 插入数据
INSERT INTO account VALUES (NULL,'小王',3000),
                           (NULL,'小刘',4000),
                           (NULL,'小李',6000),
                           (NULL,'小孙',2000);

-- 创建日志表
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 ;

-- 测试
INSERT INTO account VALUES (NULL,'阿彪',888);
SELECT * FROM account;
SELECT * FROM account_log;


-- 创建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 ;

-- 测试
UPDATE account SET money=2000 WHERE name='阿彪';
SELECT * FROM account;
SELECT * FROM account_log;


-- 创建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 ;

-- 测试DELETE型触发器
DELETE FROM account WHERE name='阿彪';
SELECT * FROM account;
SELECT * FROM account_log;

三、查看触发器

-- 查看触发器
SHOW TRIGGERS;

四、删除触发器

-- 删除触发器
DROP TRIGGER account_insert;
DROP TRIGGER account_update;
DROP TRIGGER account_delete;