Understanding the Problem with Redundant Entries in MySQL
In this article, we will delve into a common problem faced by many database administrators and developers - redundant entries after an insert trigger is fired. We will explore what causes these redundant entries, how to identify them, and most importantly, how to resolve them.
Background on MySQL Triggers
Before diving into the solution, let’s take a brief look at how MySQL triggers work. A trigger is a stored procedure that is automatically executed before or after an action is performed on a database table. In our case, we have an AFTER INSERT trigger called trg_t_received_money_item_insert_setReceivedMoneyUpdate that inserts data into the t_credit_system_money table.
The Problem with Redundant Entries
The problem arises when multiple installments are inserted into the t_received_money_item table. For example, let’s say we insert two records with an amount of 1000 for bill_customer_code = 1 and another record with a different amount. However, instead of getting unique entries in the t_credit_system_money table, we get redundant entries.
Understanding the Trigger Code
Let’s examine the trigger code to understand what might be causing this issue:
DELIMITER $$
USE `kikan_db`$$
DROP TRIGGER /*!50032 IF EXISTS */ `trg_t_received_money_item_insert_setReceivedMoneyUpdate`$$
CREATE
/*!50017 DEFINER = 'kikan'@'%' */
TRIGGER `trg_t_received_money_item_insert_setReceivedMoneyUpdate` AFTER INSERT ON `t_received_money_item`
FOR EACH ROW BEGIN
DECLARE new_order_no VARCHAR(15);
SELECT order_no INTO new_order_no FROM t_received_money WHERE `t_received_money`.bill_customer_code = new.bill_customer_code;
INSERT INTO t_credit_system_money(
data_created,
process_flag,
data_flag,
bill_customer_code,
bill_spot_customer_code,
order_no,
amount,
amount2,
credit_update_flag,
delete_flag,
create_user,
update_user,
updated,
deleted,
created
)
VALUES
(
NOW(),
1,
1,
NEW.bill_customer_code,
IFNULL(NEW.bill_spot_customer_code,0),
new_order_no,
NEW.received_money,
0,
0,
0,
NEW.update_user,
NEW.update_user,
NEW.updated,
NULL,
NEW.updated
);
END;
$$
DELIMITER ;
The trigger is designed to insert a new entry into the t_credit_system_money table based on the inserted data in the t_received_money_item table. However, we can see that it uses the NEW.bill_customer_code value directly without checking if the same customer code already exists.
Identifying the Cause of Redundant Entries
The issue lies in the fact that the trigger is using the bill_customer_code from the inserted data (new.bill_customer_code) to insert a new entry into the t_credit_system_money table. This means that every time an insertion occurs, a new entry is created for the same customer code.
To illustrate this further, let’s consider another example:
Suppose we have two records in t_received_money_item with amounts 1000 and 2000 for bill_customer_code = 1:
INSERT INTO t_received_money_item (bill_customer_code, received_money) VALUES (1, 1000);
INSERT INTO t_received_money_item (bill_customer_code, received_money) VALUES (1, 2000);
The trigger will insert two new entries into the t_credit_system_money table for bill_customer_code = 1:
+------------+-----------+----------+-------------+---------------+---------+--------+--------+-------+--------+--------+
| data_created | process_flag | data_flag | bill_customer_code | bill_spot_customer_code | order_no | amount | amount2 | credit_update_flag | delete_flag | create_user | update_user | updated | deleted | created |
+------------+-----------+----------+-------------+---------------+---------+--------+--------+-------+--------+--------+--------+
| 2023-03-01 12:34:56 | 1 | 1 | 1 | NULL | 1 | 1000.00 | 0 | 0 | NULL | kikan | kikan | 2023-03-01 12:34:56 | NULL | 2023-03-01 12:34:56 |
| 2023-03-01 12:35:21 | 1 | 1 | 1 | NULL | 2 | 2000.00 | 0 | 0 | NULL | kikan | kikan | 2023-03-01 12:35:21 | NULL | 2023-03-01 12:35:21 |
+------------+-----------+----------+-------------+---------------+---------+--------+--------+-------+--------+--------+--------+
As we can see, the trigger has inserted two redundant entries for bill_customer_code = 1.
Resolving the Issue
To resolve this issue, we need to ensure that only unique customer codes are used in the t_credit_system_money table. We can achieve this by using a UNIQUE index on the bill_customer_code column.
Here’s an updated trigger code snippet:
DELIMITER $$
USE `kikan_db`$$
DROP TRIGGER /*!50032 IF EXISTS */ `trg_t_received_money_item_insert_setReceivedMoneyUpdate`$$
CREATE
/*!50017 DEFINER = 'kikan'@'%' */
TRIGGER `trg_t_received_money_item_insert_setReceivedMoneyUpdate` AFTER INSERT ON `t_received_money_item`
FOR EACH ROW BEGIN
DECLARE new_order_no VARCHAR(15);
SELECT order_no INTO new_order_no FROM t_received_money WHERE `t_received_money`.bill_customer_code = new.bill_customer_code;
IF NOT EXISTS (
SELECT *
FROM t_credit_system_money
WHERE bill_customer_code = NEW.bill_customer_code
) THEN
INSERT INTO t_credit_system_money(
data_created,
process_flag,
data_flag,
bill_customer_code,
bill_spot_customer_code,
order_no,
amount,
amount2,
credit_update_flag,
delete_flag,
create_user,
update_user,
updated,
deleted,
created
)
VALUES
(
NOW(),
1,
1,
NEW.bill_customer_code,
IFNULL(NEW.bill_spot_customer_code,0),
new_order_no,
NEW.received_money,
0,
0,
0,
NEW.update_user,
NEW.update_user,
NEW.updated,
NULL,
NEW.updated
);
END IF;
END;
$$
DELIMITER ;
In this updated trigger code, we added a check to ensure that the bill_customer_code does not already exist in the t_credit_system_money table before inserting a new entry. This ensures that only unique customer codes are used.
Conclusion
In conclusion, redundant entries after an insert trigger is fired can be caused by several factors, including using the same column value for insertion without checking for uniqueness. By understanding the problem and taking steps to resolve it, we can ensure that our database tables maintain consistency and accuracy.
In this article, we discussed a common issue with MySQL triggers and provided a solution to resolve it. We also covered how to identify and troubleshoot similar issues in the future.
Last modified on 2023-09-15