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