亲宝软件园·资讯

展开

mysql 触发器使用

小码农叔叔 人气:0

前言

在上一篇,我们详细了解了mysql 存储过程 相关的内容,存储过程属于数据库编程的一种,使用存储过程可以在一定程度上减少程序与mysql服务的IO交互的次数,提升性能;

本篇要介绍的是mysql的触发器,也属于数据库编程的一种,相对存储过程来说,使用起来更加简单,在某些特定的场景下使用触发器,同样可以达到减少应用程序与mysql服务器交互次数从而提升性能的目的;

一、触发器简介

触发器是一种特殊的存储过程,在定义触发器时会定义触发器的触发条件,使得触发器在满足触发条件时自动执行而不需要人为调用(存储过程需要人为参与);

触发器操作的是与表有关的数据库对象,比如在insert/update/delete之前(BEFORE)或之后(AFTER),触发并执行触发器中定义的SQL语句集合;

二、触发器特点及使用场景

1、增强数据库的安全性

可以实现对用户操作数据库的限制,比如只允许用户在特定时间段内操作数据表,不允许用户对某些数据更改超过指定的范围

2、实现数据库操作的日志审计

使用触发器,可以跟踪用户对数据库的操作行为,把用户执行的一些操作自动记录到日志跟踪表中

3、实现复杂的级联操作

比如当创建一条订单数据时,需要同时往订单详情表,库存表,财务收支表等插入数据类似这样的操作就可以考虑使用触发器;

使用触发器,可以实现更加复杂的级联操作

三、触发器类似与核心参数

实际使用的时候,主要有3种类型的触发器可供选择:INSERT ,UPDATE ,和DELETE ,三种不同类型的触发器对应3种不同的使用场景;

在编写触发器的时候,有两个非常重要的参数对象,即 NEW 和 OLD,可以简单理解为,NEW 中保存并传递即将要完成插入的参数对象,而 OLD 记录的是插入或修改或删除之前的参数对象;

触发器类型与参数对象的对应关系如下所示:

触发器类型NEW 和 OLD
INSERT 型触发器NEW 表示将要或者已经新增的数据
UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器OLD 表示将要或者已经删除的数据

四、触发器语法

了解了mysql触发器相关的理论知识后,下面就来了解下触发器的使用吧;

1、创建语法

CREATE TRIGGER trigger_name				-- 定义触发器名称
BEFORE/AFTER INSERT/UPDATE/DELETE		-- 定义触发器触发时机和类型
ON tbl_name FOR EACH ROW 				-- 行级触发器
BEGIN
	trigger_stmt ;						-- 触发器实际要执行的业务逻辑
END;

2、查看触发器

SHOW TRIGGERS ;

3、删除触发器

DROP TRIGGER [schema_name.]trigger_name ;  -- 如果没有指定 schema_name,默认为当前数 据库

五、触发器使用案例

准备两张表,一张业务表,一张日志记录表,模拟当业务表数据的增删改的时候,通过触发器将日志数据写到日志表;

业务表 user

CREATE TABLE `user` (
  `user_id` varchar(32) NOT NULL COMMENT '用户ID',
  `user_name` varchar(64) DEFAULT NULL COMMENT '昵称,表示用户真实姓名',
  `passwd` varchar(64) NOT NULL COMMENT '密码',
  `email` varchar(64) DEFAULT NULL COMMENT '邮箱',
  `mobile` varchar(32) DEFAULT NULL COMMENT '手机号',
  `address` varchar(128) DEFAULT NULL COMMENT '手机号',
  `ID` varchar(18) DEFAULT NULL COMMENT 'SFZ号',
  `sex` int(11) DEFAULT NULL COMMENT '用户性别 1:男 2:女',
  `info` varchar(255) DEFAULT NULL,
  `age` int(12) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_name` (`user_name`),
  KEY `idx_mobile` (`mobile`),
  FULLTEXT KEY `info` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

日志表

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类型 触发器使用

CREATE TRIGGER user_insert_trigger 
	after insert on `user` for each row 
begin 
	insert INTO user_logs (
		id,
		operation,
		operate_time,
		operate_id,
		operate_params
	)
values
	(
		null,
		'insert',
		now(),
		new.user_id,
		concat(
			'insert params: id=',
			new.user_id,

			',user_name = ',
			new.user_name,

			', passwd=',
			NEW.passwd,

			', email=',
			NEW.email,

			', mobile=',
			NEW.mobile,

			', address=',
			NEW.address

		)
	);
END;O user_logs (
		id,
		operation,
		operate_time,
		operate_id,
		operate_params
	)
values
	(
		null,
		'insert',
		now(),
		new.id,
		concat(
			'insert params: id=',
			new.id,

			',user_name = ',
			new.user_name,

			', passwd=',
			NEW.passwd,

			', email=',
			NEW.email,

			', mobile=',
			NEW.mobile,

			', address=',
			NEW.address

		)
	);
END;

创建完毕后,查看下刚刚创建的触发器

SHOW TRIGGERS ;

在这里插入图片描述

接下来通过给user表插入一条数据

insert into user(user_id,user_name,passwd,email,mobile,address)
values ('5','xiaowang','123456','xiaowang@qq.com','13325556761','杭州市余杭区')

数据插入成功后,检查日志表是否有数据写入

在这里插入图片描述

可以看到日志数据写入成功,说明触发器被触发了;

在这里插入图片描述

七、UPDATE 类型触发器使用

执行下面的语句进行触发器的创建

CREATE TRIGGER user_update_trigger AFTER UPDATE ON `user` FOR EACH ROW
BEGIN
	INSERT INTO user_logs (
		id,
		operation,
		operate_time,
		operate_id,
		operate_params
	)
VALUES
	(
		NULL,
		'update',
		now(),
		new.user_id,
		concat(
			'before update params: id=',
			OLD.user_id,

			',user_name=',
			OLD.user_name,

			', passwd=',
			OLD.passwd,

			', email=',
			OLD.email,

			', mobile=',
			OLD.mobile,

			' | after update params: id=',
			NEW.user_id,

			',user_name=',
			NEW.user_name,

			', passwd=',
			NEW.passwd,

			', email=',
			NEW.email,

			', mobile=',
			NEW.mobile
		)
	);
end;

在这里插入图片描述

然后执行下面的 update语句验证下该类型的触发器是否生效;

update `user` set user_name = 'xiaowang_1',passwd = '123456',email = 'xiaowang_update@qq.com' where user_id= '5';

cja

在这里插入图片描述

八、DELETE 类型触发器使用

执行下面的sql创进行触发器创建

create trigger user_delete_trigger
	after delete on `user` for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
	(null, 'delete', now(), old.user_id,
concat('before delete: id=',old.user_id,',user_name=',old.user_name, ', mobile=', old.mobile, ', email=', old.email, ', address=', old.address));
end;

在这里插入图片描述

触发器创建完毕后,执行下面的delete sql,观察日志表是否有一条新增的数据

delete from user where user_id = ‘2’;

在这里插入图片描述

通过日志表发现,删除的触发器生效了;

九、触发器常用场景

1、使用触发器实现两表或多表数据同步

举例来说,现在有A表和A_copy表,A_copy表的存在的目的相对于是备份表,存储了A表中的关键业务字段,应用程序向A表插入一条数据时,需同步向A_copy表插入一条数据,这样的业务场景就可以考虑使用触发器;

2、审计日志记录

如上面的案例,向核心业务主表进行增删改操作时,记录审计日志可以考虑使用触发器;

3、合规性检查

比如向核心业务表添加涉及到金钱相关的数据时,可以设定一定的检查规则,比如当金额超过一定的数量时,及时提示错误,防止错误数据进入系统;

十、触发器使用注意点

合理利用触发器可以帮助应用程序减少与数据库的IO次数,一定程度上提升性能,但是触发器也有一些自身的缺点,在使用的时候需要注意,现做如下总结,

1、可读性较差

触发器最大的一个问题就是可读性差,因为触发器存储在数据库中,并且由事件驱动,这就意味着触发器有可能不受应用层的控制 ,这对系统维护是非常有挑战的;

2、相关数据的变更,可能会导致触发器出错

特别是涉及到数据的表结构变更,都有可能导致触发器出错,进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性,影响到应用中错误原因排查的效率;

加载全部内容

相关教程
猜你喜欢
用户评论