同一表上的Before Insert和After Update触发器工作不正常

Question

亲爱的先生，我有一个主表tbl_applic_basic_info，我在这个表上创建了两种类型的触发器，一种是在插入之后，另一种是更新触发器之前。我在主表中插入第一条记录意味着当主键是一个主键时，它在主表插入记录。在插入触发器之前，在我单独创建的tbl_applic_basic_info_after_insert中插入记录。其次，当我更新主键一条记录时，比如在主表中我插入名称，即ab，现在我将其更新为abc，更新后触发器在另一个表中插入数据，即tbl_applic_basic_info_after_update，我现在也单独创建了它。问题是，当我更新同一数据两次时，比如名称是abc，现在我更改了它abcxzy，它不允许我更新记录。我使用插入后触发器的主要目的是，一旦它插入主表，它就会维护记录更新触发器保持单个用户的记录有多少次更新不同行的记录我无法操作代码请更正我的代码我会非常满意的。

DROP TABLE IF EXISTS `tbl_appli_basic_info`;
CREATE TABLE IF NOT EXISTS `tbl_appli_basic_info` (
`appli_basic_info_id` int(22) NOT NULL AUTO_INCREMENT,
`apli_reg_no` int(22) NOT NULL,
`full_name` varchar(256) NOT NULL,
PRIMARY KEY (`appli_basic_info_id`),
KEY `apli_reg_no` (`apli_reg_no`)
);
CREATE TABLE IF NOT EXISTS `tbl_appli_basic_info_after_insert` (
`appli_basic_info_id` int(22) NOT NULL AUTO_INCREMENT,
`apli_reg_no` int(22) NOT NULL,
`full_name` varchar(256) DEFAULT NULL,
PRIMARY KEY (`appli_basic_info_id`),
KEY `apli_reg_no` (`apli_reg_no`)
) ;
CREATE TABLE IF NOT EXISTS `tbl_appli_basic_info_after_update` (
`appli_basic_info_id` int(22) NOT NULL AUTO_INCREMENT,
`apli_reg_no` int(22) NOT NULL,
`full_name` varchar(256) NOT NULL,
PRIMARY KEY (`appli_basic_info_id`)
);
DROP TRIGGER IF EXISTS `trg_appli_basic_info_after_insert`;
DELIMITER $$
CREATE TRIGGER `trg_appli_basic_info_after_insert` AFTER INSERT ON `tbl_appli_basic_info` FOR EACH ROW BEGIN
-- Insert record into tbl_appli_basic_info_after_insert table
INSERT INTO tbl_appli_basic_info_after_insert
( appli_basic_info_id,
apli_reg_no,
full_name)
VALUES
( NEW.appli_basic_info_id,
NEW.apli_reg_no,
NEW.full_name
);
END
$$
DELIMITER ;
DROP TRIGGER IF EXISTS `trg_appli_basic_info_before_update`;
DELIMITER $$
CREATE TRIGGER `trg_appli_basic_info_before_update` BEFORE UPDATE ON `tbl_appli_basic_info` FOR EACH ROW BEGIN
-- Insert record into audit table
INSERT INTO tbl_appli_basic_info_before_update
( appli_basic_info_id,
apli_reg_no,
full_name
)
VALUES
( NEW.appli_basic_info_id,
NEW.apli_reg_no,
NEW.full_name
);
END
$$
DELIMITER ;

Answer 1

您的审计表定义没有多大意义，因为您定义的是appli_basic_info_idint(22(NOT NULL AUTO_INCREMENT主键并在触发器中覆盖它，因此我预计会出现重复的键错误。我会将表格定义更改为

CREATE TABLE IF NOT EXISTS `tbl_appli_basic_info_after_insert` (
id int auto_increment primary key,
`appli_basic_info_id` int(22) ,
`apli_reg_no` int(22) NOT NULL,
`full_name` varchar(256) DEFAULT NULL,
PRIMARY KEY (id),
KEY `apli_reg_no` (`apli_reg_no`)
) ;
CREATE TABLE IF NOT EXISTS `tbl_appli_basic_info_after_update` (
id int auto_increment primary key,
`appli_basic_info_id` int(22) NOT NULL ,
`apli_reg_no` int(22) NOT NULL,
`full_name` varchar(256) NOT NULL,
PRIMARY KEY (id)
);