如何配置触发器实现Payments表新增数据时自动更新total_balance表用户总余额

First, let’s align on the assumed table structures (adjust these if your actual schema differs):

• Payments: Contains username (unique identifier for users), Status (payment status, e.g., 'Complete'), and payment_amount (the amount of the payment)
• total_balance: Contains username (primary/unique key to avoid duplicate entries) and total_money (sum of completed payments for the user)

The core logic here is: every time a new record is inserted into Payments, we recalculate the total of all Complete payments for the affected user, then either insert a new row into total_balance (if the user doesn’t exist there yet) or update the existing row’s total_money value.

Step 1: Create the Trigger (MySQL Example) #

Here’s a ready-to-use trigger that implements this logic:

DELIMITER //
CREATE TRIGGER sync_total_balance_after_payment_insert
AFTER INSERT ON Payments
FOR EACH ROW
BEGIN
    -- Calculate total completed payments for the user who just had a payment inserted
    INSERT INTO total_balance (username, total_money)
    SELECT NEW.username, SUM(payment_amount)
    FROM Payments
    WHERE username = NEW.username AND Status = 'Complete'
    ON DUPLICATE KEY UPDATE total_money = VALUES(total_money);
END //
DELIMITER ;

Let’s Break Down What This Does: #

• AFTER INSERT ON Payments: The trigger runs after a new record is added to the Payments table (so we can include the new payment in our sum).
• FOR EACH ROW: Ensures the trigger runs once for every single row inserted (works for both single and bulk inserts).
• INSERT ... ON DUPLICATE KEY UPDATE: This is the magic that handles both new and existing users:
  • If the user doesn’t exist in total_balance, it inserts a new row with their total completed payments.
  • If the user already exists, it updates their total_money to the newly calculated sum.

Example Walkthrough #

Let’s test with your sample scenario:

1. Insert John’s first completed payment: INSERT INTO Payments (username, Status, payment_amount) VALUES ('John', 'Complete', 100);
   • The trigger runs, calculates John’s total as 100, and inserts ('John', 100) into total_balance.
2. Insert John’s second completed payment: INSERT INTO Payments (username, Status, payment_amount) VALUES ('John', 'Complete', 50);
   • The trigger recalculates John’s total as 150, and updates his total_money in total_balance to 150.
3. Insert Alex’s completed payment: INSERT INTO Payments (username, Status, payment_amount) VALUES ('Alex', 'Complete', 100);
   • The trigger inserts ('Alex', 100) into total_balance.

Key Notes to Ensure This Works: #

• Unique Key Requirement: Make sure username is set as a primary key or unique key in total_balance—this is what makes ON DUPLICATE KEY UPDATE function correctly.
• Data Type Consistency: Use DECIMAL (instead of FLOAT) for payment_amount and total_money to avoid floating-point precision errors with currency values.
• Case Sensitivity: Verify that the Status value matches exactly (e.g., if your status is stored as 'complete' lowercase, adjust the trigger’s Status = 'Complete' to match).
• Extending to Updates/Deletes: If you need to sync when a payment’s status is updated (e.g., from 'Pending' to 'Complete') or when a payment is deleted, you can create similar triggers using AFTER UPDATE and AFTER DELETE on the Payments table, using the same INSERT ... ON DUPLICATE KEY UPDATE logic.

内容的提问来源于stack exchange，提问作者user11109861