【DB】数据库使用触发器

╰+哭是因爲堅強的太久メ 2022-05-19 06:51 223阅读 0赞

## 一、情况说明 ##

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

delimiter (意思是告诉mysql语句的结尾换成以  ( 意 思 是 告 诉 m y s q l 语 句 的 结 尾 换 成 以 结束)

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';

*  观察结果user表和user\_history表的结果，操作记录已产生，说明触发器工作正常；

# 弊端 #

增加程序的复杂度，有些业务逻辑在代码中处理，有些业务逻辑用触发器处理，会使后期维护变得困难；

# 六、小结 #

就是因为dba添加了触发器，进行表的update记录，所以，当更新的时候，就会向备份表里面插入数据，然而id是相同的，所以就会出现主键重复的错误。触发器耗性能。