mysql触发器的设置

– 创建数据库db7

CREATE DATABASE db7 CHARSET=utf8;

– 创建账户表account

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),	-- 姓名
	money DOUBLE       	-- 余额
);

– 添加数据

INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);

– 创建日志表

CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,
	operation VARCHAR(20), 		-- 操作类型 （insert update delete）
	operation_time DATETIME,	-- 操作时间
	operation_id INT,		-- 操作表id
	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,'}'));
END$
DELIMITER ;

– 向account表添加一条记录

INSERT INTO account VALUES(NULL,'张三',2000);

更新触发器

– 创建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 NAME='李四';