MySql数据库触发器DML（数据库操作）

1.数据库触发器的作用是什么？

触发器主要用于强制复杂的业务规则和要求,还有助于强制引用的完整性,便于在添加,更新或删除表中的行时保留表之间已定义的关系。

2.新建两张表

create table zhu(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(8),
gender char(2),
age int(11)
);

CREATE TABLE zhu_log(
id INT PRIMARY KEY AUTO_INCREMENT,
time TIMESTAMP,
operation VARCHAR(10),
detail VARCHAR(50)
);

INSERT INTO zhu(name,gender,age) VALUES ('张三', '男', 22);

3.做一个插入触发器

//插入
CREATE TRIGGER trigger_insert
AFTER INSERT ON zhu FOR EACH ROW
INSERT INTO zhu_log(time,operation,detail)
VALUES(NOW(),'INSERT',CONCAT('新纪录: ',NEW.id,',',NEW.name,',',NEW.gender,',',NEW.age));

4.做一个更改触发器

//更新
CREATE TRIGGER trigger_update
AFTER UPDATE ON zhu FOR EACH ROW
INSERT INTO zhu_log(time,operation,detail)
VALUES(NOW(),'UPDATE',CONCAT('(',OLD.id,',',OLD.name,',',OLD.gender,',',OLD.age,')','->','(',NEW.id,',',NEW.name,',',NEW.gender,',',NEW.age,')'));

5.做一个删除触发器

//删除
CREATE TRIGGER trigger_delete
AFTER DELETE ON zhu FOR EACH ROW
INSERT INTO zhu_log(time,operation,detail)
VALUES(NOW(),'DELETE',CONCAT('旧纪录: ',OLD.id,',',OLD.name,',',OLD.gender,',',OLD.age));

6.检查三个触发器是否成功

show TRIGGERS;