1. 触发器概念
- 触发器是与表有关的数据库对象,可以在 insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。
- 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。
触发器类型 | OLD的含义 | NEW的含义 |
---|---|---|
INSERT 型触发器 | 无 (因为插入前状态无数据) | NEW 表示将要或者已经新增的数据 |
UPDATE 型触发器 | OLD 表示修改之前的数据 | NEW 表示将要或已经修改后的数据 |
DELETE 型触发器 | OLD 表示将要或者已经删除的数据 | 无 (因为删除后状态无数据) |
2. 数据准备
-- 创建db_trigger数据库
CREATE DATABASE db_trigger;
-- 使用db_trigger数据库
USE db_trigger;
-- 创建账户表account
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT, -- 账户id
NAME VARCHAR(20), -- 姓名
money DOUBLE -- 余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'李白',1000),(NULL,'杜甫',1000);
-- 创建日志表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) -- 操作参数
);
3. 创建INSERT型触发器
-- 创建INSERT型触发器。用于对account表新增数据进行日志的记录
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,'李清照',2000);
-- 查询account表
SELECT * FROM account;
-- 查询account_log表
SELECT * FROM account_log;
4. 创建UPDATE型触发器
-- 创建UPDATE型触发器。用于对account表修改数据进行日志的记录
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表中杜甫的金额为2000
UPDATE account SET money=2000 WHERE id=2;
-- 查询account表
SELECT * FROM account;
-- 查询account_log表
SELECT * FROM account_log;
5. 创建DELETE型触发器
-- 创建DELETE型触发器。用于对account表删除数据进行日志的记录
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;
-- 查询account_log表
SELECT * FROM account_log;
6. 查看和删除触发器
/*
查看触发器
SHOW TRIGGERS;
*/
-- 查看触发器
SHOW TRIGGERS;
/*
删除触发器
DROP TRIGGER 触发器名称;
*/
-- 删除account_delete触发器
DROP TRIGGER account_delete;
如有错误欢迎留言评论,2021年7月12日 羽露风