Mysql 触发器实践

前言

本文以Mysql实践应用展示如何用触发器定位数据库中表记录是什么时间被修改、删除的。

一、创建一个日志表，用于记录被更新或删除的数据

后面两个字段，opr_datetime记录的触发更新或删除的时间值
opr_type用于标识数据被操作类型，11表示被删除,21表示更新前的记录值，22表示更新后的记录值。

CREATE TABLE `user_orgrole_menu_ref_log` (
  `id` int(11) NOT NULL  COMMENT '唯一主键',
  `org_role_code` bigint(20) DEFAULT NULL COMMENT '组织角色编码',
  `menu_code` bigint(20) DEFAULT NULL COMMENT '菜单编码',
  `root_node` tinyint(4) DEFAULT '0' COMMENT '根节点（0 否 1是）',
  `ancestral_code` bigint(20) DEFAULT '0' COMMENT '祖级编码',
  `created_id` bigint(20) DEFAULT NULL COMMENT '创建人id',
  `created_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人姓名',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_id` bigint(20) DEFAULT NULL COMMENT '修改人id',
  `modified_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '修改人姓名',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `opr_datetime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '触发器记录的更新时间值',
  `opr_type` int(11) DEFAULT NULL COMMENT '操作类型，11删除,21更新前，22更新后'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='组织角色菜单映射表日志';

二、创建删除前触发器

-- 被删除数据保存至触发器日志表中
CREATE TRIGGER  user_orgrole_delete_before 
BEFORE DELETE ON user_orgrole_menu_ref FOR EACH ROW 
BEGIN
    INSERT INTO user_orgrole_menu_ref_log 
		(		id,
				org_role_code,
				menu_code,
				root_node,
				ancestral_code,
				created_id,
				created_by,
				created_at,
				modified_id,
				modified_by,
				modified_at,
				opr_datetime,
				opr_type)
    VALUES (id,
				old.org_role_code,
				old.menu_code,
				old.root_node,
				old.ancestral_code,
				old.created_id,
				old.created_by,
				old.created_at,
				old.modified_id,
				old.modified_by,
				old.modified_at,
				NOW(),
				11);
END
 

三、创建更新前触发器

-- 更新前数据先保存至触发器日志表中
CREATE TRIGGER  user_orgrole_update_before 
BEFORE UPDATE ON user_orgrole_menu_ref FOR EACH ROW 
BEGIN
    INSERT INTO user_orgrole_menu_ref_log 
		(		id,
				org_role_code,
				menu_code,
				root_node,
				ancestral_code,
				created_id,
				created_by,
				created_at,
				modified_id,
				modified_by,
				modified_at,
				opr_datetime,
				opr_type)
    VALUES (id,
				old.org_role_code,
				old.menu_code,
				old.root_node,
				old.ancestral_code,
				old.created_id,
				old.created_by,
				old.created_at,
				old.modified_id,
				old.modified_by,
				old.modified_at,
				NOW(),
				21);
END

四、创建更新后触发器

-- 更新后数据保存至触发器日志表中
CREATE TRIGGER  user_orgrole_update_after 
AFTER UPDATE ON user_orgrole_menu_ref FOR EACH ROW 
BEGIN
    INSERT INTO user_orgrole_menu_ref_log 
		(		id,
				org_role_code,
				menu_code,
				root_node,
				ancestral_code,
				created_id,
				created_by,
				created_at,
				modified_id,
				modified_by,
				modified_at,
				opr_datetime,
				opr_type)
    VALUES (id,
				new.org_role_code,
				new.menu_code,
				new.root_node,
				new.ancestral_code,
				new.created_id,
				new.created_by,
				new.created_at,
				new.modified_id,
				new.modified_by,
				new.modified_at,
				NOW(),
				22);
END

五、查看创建的触发器

• 在navicat 对应的表上右键，选择设计表
  
  然后在打开界面，切换到触发器选项卡，点击触发器名
  

六、有删除或更新操作后，查看日志表记录

-- 更新一条记录
UPDATE user_orgrole_menu_ref a1 SET a1. root_node = 127 WHERE a1.id = 3673125;
-- 删除一条记录
DELETE A1 FROM user_orgrole_menu_ref a1 WHERE a1.id = 3673125;
-- 查看触发器记录的日志
SELECT * FROM user_orgrole_menu_ref_log;

七、删除触发器

-- 删除触发器
DROP TRIGGER user_orgrole_update_after