MySQL触发器

触发器

触发器的分类

触发器的操作

创建触发器

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

示例

CREATE DATABASE db7;
USE db7;
-- 创建表数据
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(50),
	money DOUBLE
);
INSERT INTO account VALUES(NULL,'张三',1000), (NULL,'李四',1000);

CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,
	operation VARCHAR(20),
	operation_time DATETIME,
	operation_id INT,
	operation_params VARCHAR(200)
);

-- 创建一个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,'}'));
	-- new.id  new.name  new.money 用来获取新增后的数据
END$
DELIMITER ;

-- account里面添加一条记录
INSERT INTO account VALUES(NULL,'王五',1100);
-- 查询account表
SELECT * FROM account;
-- 查询account_log表
SELECT * FROM account_log;

-- 创建一个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 ;
-- 修改李四金额为2000
UPDATE account SET money=2000 WHERE id=2;

SELECT * FROM account;

SELECT * FROM account_log;

-- 创建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 ;
DELETE FROM account WHERE id=3;
SELECT * FROM account;
SELECT * FROM account_log;

查看和删除触发器

-- 查看触发器
SHOW TRIGGERS;
-- 删除触发器
DROP TRIGGER IF EXISTS account_delete;