mysql日志触发器

1.创建数据库

create database test character set utf8 collate utf8_bin;

2.使用数据库，并创建数据表t_student

2.使用数据库
use test;
3.创建数据表t_student 记录id,name ,gender ,age
CREATE table t_student(
id INT(4) PRIMARY KEY,
name VARCHAR(4) not NULL,
gender VARCHAR(2) DEFAULT '男',
age VARCHAR(2) not NULL
);

3.忘t_student插入五条数据

5.往t_student里插入五条记录
INSERT INTO t_student(id,name,gender,age) VALUES (1,'王老六','男',25),
(2,'王老五','男',24),
(3,'王老四','男',23),
(4,'王老三','男',22),
(5,'王老二','男',21);

4.创建日志表

6.创建日志表
CREATE table log(
id INT PRIMARY KEY auto_increment,
time TIMESTAMP,
operation VARCHAR(10) ,
detail VARCHAR(50)
);

5.创建触发器

7.创建触发器
CREATE TRIGGER trigger_insert
	AFTER INSERT on t_student FOR each row
	INSERT INTO log (time,operation,detail)
			VALUES (NOW(), 'INSERT', CONCAT('新记录：',NEW.id,NEW.name,NEW.gender,NEW.age));

8.创建更新触发器

8.创建更新触发器
CREATE TRIGGER trigger_update 
		AFTER UPDATE ON t_student FOR EACH ROW
		INSERT INTO log ( time, operation, detail)
		VALUES (NOW(), 'UPDATE', CONCAT('(',
				OLD.id, OLD.NAME, OLD.gender, OLD.age, ')',
				'->', '(', NEW.id, NEW.name, NEW.gender, NEW.age, ')'));

9.创建删除触发器

9.创建删除触发器
CREATE TRIGGER trigger_delete 
		AFTER DELETE ON t_student FOR EACH ROW
		INSERT INTO log ( time, operation, detail)
				VALUES (NOW(), 'DELETE', CONCAT('旧记录：',old.id,old.name,old.gender,old.age));

10.查看创建的触发器

(1).查看触发器

SHOW TRIGGERS;

(1).查看触发器的命令语句

11.查看触发器的命令语句
SHOW CREATE TRIGGER trigger_insert;

12.查看触发器的命令语句
SHOW CREATE TRIGGER trigger_update;

13.查看触发器的命令语句
SHOW CREATE TRIGGER trigger_delete;

11.测试插入触发器

1.插入一条数据

14.测试触发器
INSERT INTO t_student(id,name,gender,age) VALUES (6,'王经','男',25);

查看触发表

2.更新数据

UPDATE t_student set name = '王老七', gender='男',age=21 WHERE id=6;

查看数据表

3.删除数据

删除
DELETE FROM t_student WHERE id=6;

12.删除触发器

删除触发器
DROP TRIGGER trigger_insert;
DROP TRIGGER trigger_update;
DROP TRIGGER trigger_delete;