【DB】数据库使用触发器

原创

wx645606081424e 2023-05-06 17:04:22 ©著作权

文章标签 数据库 java 服务器触发器数据 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者wx645606081424e的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、情况说明

今天调用account服务，进行账户更新。其中有两步更新同一个表：

1.更新t_account_event的状态
2.更新t_account_event的基本信息

因为在t_account_event表中添加了触发器，每当有更新操作，完成更新后，就会向sync_t_account_event表中插入一条数据，插入的数据为更新后的数据内容。

二、问题

因为是两次更新，所以更新的时候，插入的数据的主键id是相同的！！所以会报主键冲突的错误：

2020-06-21 03:09:45.911 ERROR [account-worker,b5ca36ca438fb665,b5ca36ca438fb665,false] 16424 --- [ecutor-woker-25] druid.sql.Statement                      : {conn-10010, pstmt-20160} execute error. UPDATE t_account_event
	 SET result_info= ?,
		 status= ?,
		 update_user= ?,
		 update_datetime= ? 
	WHERE id = ?

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '4978199937876992' for key 'PRIMARY'

三、附加问题

t_account_event表中，存在四个字段的唯一索引。

当进行更新操作的时候，就会同样报索引

2020-06-21 04:02:36.144 ERROR [account-worker,000e4bf732ea2325,000e4bf732ea2325,false] 16424 --- [ecutor-woker-52] druid.sql.Statement                      : {conn-10010, pstmt-20165} execute error. UPDATE t_account_event
	 SET result_info= ?,
		 status= ?,
		 update_user= ?,
		 update_datetime= ? 
	WHERE id = ?

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'capital-P2P_T000016202006210402355281703-modify-1' for key 'order_id'

触发器实例代码

CREATE TABLE `t_account_event` (
	`id` VARCHAR (32) NOT NULL COMMENT '主键',
	`create_datetime` datetime DEFAULT NULL COMMENT '创建时间',
	`update_datetime` datetime DEFAULT NULL COMMENT '更新时间',
	`create_user` VARCHAR (50) DEFAULT NULL COMMENT '创建人',
	`update_user` VARCHAR (50) DEFAULT NULL COMMENT '更新人',
	`account_type` VARCHAR (10) NOT NULL COMMENT '账户类型,asset:资产账户,capital:资金账户,debt:负债账户,账户类型,activity:活动账户,cost:费用账户,advance:垫资账户,interest:利息账户,deposit:保证金账户,ecel:智享账户',
	`order_id` VARCHAR (40) NOT NULL COMMENT '订单编号',
	`status` VARCHAR (2) DEFAULT NULL COMMENT '处理状态0：待处理,1：处理中,2：已处理,2：处理失败:-1',
	`result_info` LONGTEXT COMMENT '处理结果',
	`param` LONGTEXT COMMENT '请求参数',
	`busi_status` VARCHAR (2) DEFAULT NULL COMMENT '交易状态 ：1-支出 ,2-支付-冻结 ,3-支付-解冻,4-支付-解冻回退,5-收入,6-收入-冻结,7-收入-解冻,8-收入-解冻回退',
	`event_type` VARCHAR (20) NOT NULL COMMENT '事件类型：open：开户,card：绑卡，open_card：绑卡,modify：账户变更',
	`cou` INT (11) DEFAULT '3' COMMENT '重试次数',
	`synid` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	UNIQUE KEY `order_id` (
		`account_type`,
		`order_id`,
		`event_type`,
		`busi_status`
	),
	KEY `idx_ad_syid` (`synid`)
) ENGINE = INNODB AUTO_INCREMENT = 268 DEFAULT CHARSET = utf8 COMMENT = '账户事件操作表';

CREATE TRIGGER `tri_t_account_event` AFTER UPDATE ON `t_account_event` FOR EACH ROW
BEGIN
	INSERT INTO sync_t_account_event (
		id,
		create_datetime,
		update_datetime,
		create_user,
		update_user,
		account_type,
		order_id,
		STATUS,
		result_info,
		param,
		busi_status,
		event_type,
		cou
	)
VALUES
	(
		new.id,
		new.create_datetime,
		new.update_datetime,
		new.create_user,
		new.update_user,
		new.account_type,
		new.order_id,
		new. STATUS,
		new.result_info,
		new.param,
		new.busi_status,
		new.event_type,
		new.cou
	);


END;

四、触发器知识

什么是触发器

简单的说，就是一张表发生了某件事（插入、删除、更新操作），然后自动触发了预先编写好的若干条SQL语句的执行；
##特点及作用
特点：触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行；

作用：保证数据的完整性，起到约束的作用；
##例子：创建触发器，记录表的增、删、改操作记录
接下来将创建user和user_history表，以及三个触发器tri_insert_user、tri_update_user、tri_delete_user，分别对应user表的增、删、改三件事；

创建user表；
创建对user表操作历史表；

DROP TABLE IF EXISTS `user_history`;
CREATE TABLE `user_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `operatetype` varchar(200) NOT NULL,
  `operatetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建user表插入事件对应的触发器tri_insert_user；

几点说明：

DELIMITER：改变输入的结束符，默认情况下输入结束符是分号;，这里把它改成了两个分号;;，这样做的目的是把多条含分号的语句做个封装，全部输入完之后一起执行，而不是一遇到默认的分号结束符就自动执行；
如：

$【DB】数据库使用触发器_服务器$ 结束)

create trigger tg1
after insert on o
for each row 
begin
update g set num=num-3 where id=1;
end$

new：当触发插入和更新事件时可用，指向的是被操作的记录
对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。

create trigger tg2
	after insert on o
	for each row 
	begin
	update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同)
	end$

删除，
对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。

drop trigger tg1$

对于update而言：被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中的值；

修改的后的数据，用new来表示，new.列名引用被修改之后行中的值。

old：当触发删除和更新事件时可用，指向的是被操作的记录

Old.feild 就是更新前的数据

New.feild 就是更新后的数据

DROP TRIGGER IF EXISTS `tri_insert_user`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_user` AFTER INSERT ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user',  now());
end
;;
DELIMITER ;

创建user表更新事件对应的触发器tri_update_user；

DROP TRIGGER IF EXISTS `tri_update_user`;
DELIMITER ;;
CREATE TRIGGER `tri_update_user` AFTER UPDATE ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id,operatetype, operatetime) VALUES (new.id, 'update a user', now());
end
;;
DELIMITER ;

创建user表删除事件对应的触发器tri_delete_user；

DROP TRIGGER IF EXISTS `tri_delete_user`;
DELIMITER ;;
CREATE TRIGGER `tri_delete_user` AFTER DELETE ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', now());
end
;;
DELIMITER ;

至此，全部表及触发器创建完成，开始验证结果，分别做插入、修改、删除事件，执行以下语句，观察user_history是否自动产生操作记录；

INSERT INTO user(account, name, address) VALUES ('user1', 'user1', 'user1');
INSERT INTO user(account, name, address) VALUES ('user2', 'user2', 'user2');
UPDATE user SET name = 'user3', account = 'user3', address='user3' where name='user1';
DELETE FROM `user` where name = 'user2';