触发器

• 触发器
• • 语法
  • 示例-insert型触发器
  • 示例-update型触发器
  • 示例-delete型触发器

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前（BEFORE）或之后（AFTER），触发并执行触发器中定义的SQL语句集合。

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发时机：BEFORE、AFTER。

语法

创建触发器：

CREATE TRIGGER 触发器名称
触发时机 触发类型
ON 表名 FOR EACH ROW
BEGINsql逻辑;
END;

查看触发器：
SHOW TRIGGERS;

删除当前库触发器：
DROP TRIGGER 触发器名;

删除指定库的触发器：
DROP TRIGGER 库名.触发器名;

示例，数据准备：

CREATE TABLE tb_user(id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',name VARCHAR(50) NOT NULL COMMENT '用户名',phone VARCHAR(11) NOT NULL COMMENT '手机号',email VARCHAR(100) COMMENT '邮箱',profession VARCHAR(11) COMMENT '专业',age TINYINT UNSIGNED COMMENT '年龄',gender CHAR(1) COMMENT '性别, 1: 男, 2: 女',user_status CHAR(1) COMMENT '状态',createtime DATETIME COMMENT '创建时间'
) COMMENT '系统用户表';INSERT INTO tb_user (name, phone, email, profession, age, gender, user_status, createtime) 
VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00'), ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00'), ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00'), ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00'), ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00'), ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00'), ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00'), ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00'), ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00'), ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00'), ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00'), ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00'), ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00'), ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00'), ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00'), ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00'), ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00'), ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00'), ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00'), ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00'), ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00'), ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00'), ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00'), ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');# 日志表 user_logs
CREATE TABLE user_logs(id INT(11) NOT NULL AUTO_INCREMENT,operation VARCHAR(20) NOT NULL COMMENT '操作类型, insert/update/delete',operate_time DATETIME NOT NULL COMMENT '操作时间',operate_id INT(11) NOT NULL COMMENT '操作的ID',operate_params VARCHAR(500) COMMENT '操作参数',PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

示例-insert型触发器

以语法中的tb_user表与user_logs表为例：

# 通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中（插入数据触发器）
CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON tb_user FOR EACH ROW
BEGININSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)VALUES(NULL, 'insert', NOW(), NEW.id, CONCAT('插入的数据内容为: id=', NEW.id, ',name=', NEW.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
END;/*
测试
*/
# 查看触发器
SHOW triggers;# 插入数据到tb_user
INSERT INTO tb_user(id, name, phone, email, profession, age, gender, user_status, createtime) 
VALUES (26, '三皇子', '18809091212', 'erhuangzi@163.com', '软件工程', 23, '1', '1', NOW());# 测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。

示例-update型触发器

以语法中的tb_user表与user_logs表为例：

# 通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中（更新数据触发器）
CREATE TRIGGER tb_user_update_trigger
AFTER UPDATE ON tb_user FOR EACH ROW
BEGININSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)VALUES(NULL, 'update', NOW(), NEW.id, CONCAT('更新之前的数据: id=', OLD.id, ',name=', OLD.name, ', phone=', OLD.phone, ', email=', OLD.email, ', profession=', OLD.profession, ' | 更新之后的数据: id=', NEW.id, ', name=', NEW.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
END;/*
测试
*/
# 查看触发器
SHOW triggers;# 更新tb_user表数据
UPDATE tb_user SET profession = '会计' WHERE id <= 5;# 测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。

示例-delete型触发器

以语法中的tb_user表与user_logs表为例：

# 通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中（更新数据触发器）
CREATE TRIGGER tb_user_delete_trigger
AFTER DELETE ON tb_user FOR EACH ROW
BEGININSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)VALUES(NULL, 'delete', NOW(), OLD.id, CONCAT('删除之前的数据: id=', OLD.id, ', name=', OLD.name, ', phone=', OLD.phone, ', email=', OLD.email, ', profession=', OLD.profession));
END;/*
测试
*/
# 查看触发器
SHOW triggers;# 删除tb_user表数据
DELETE FROM tb_user WHERE id = 26;# 测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。