MySql 觸發器在批量插入期間具有空變數

我有一個 SQL 觸發器。（mysql）。

當我進行批量插入時，總和值變為空，

PS。單個插入工作。批量插入不工作。

DROP TRIGGER IF EXISTS  `calculate_balances`;


CREATE TABLE  IF NOT EXISTS temp(account_no VARCHAR(60),amount DECIMAL  (10,2));

CREATE TRIGGER `calculate_balances` AFTER INSERT ON `transactions` FOR EACH ROW
 BEGIN

SET @credit = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_to = New.account_to);
SET @debit  = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_from = New.account_to);
SET @credit_balance = @credit - @debit;

SET @credit2 = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_to = New.account_from);
SET @debit2  = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_from = New.account_from);
SET @debit_balance = @credit2 - @debit2;

#     UPDATE `accounts` SET account_balance = @debit_balance WHERE account_no =  New.account_from;
#     UPDATE `accounts` SET account_balance = @credit_balance WHERE account_no =  New.account_to;

INSERT INTO temp VALUES(New.account_from,@debit_balance);
INSERT INTO temp VALUES(New.account_to,@credit_balance);



END

所以我需要弄清楚為什麼會發生這種情況並解決它。

看起來我需要照顧空，

這是我所做的，儘管列可能已經改變

CREATE TRIGGER calculate_balances AFTER INSERT ON transactions FOR EACH ROW
            BEGIN

               SET @credit = (SELECT SUM(amount) FROM transactions WHERE  transactions.credit = New.credit);
               SET @debit  = (SELECT SUM(amount) FROM transactions WHERE  transactions.debit = New.credit);

               IF @debit IS NULL
               THEN
                   SET @debit = 0;
               END IF;

               IF @credit IS NULL
               THEN
                   SET @credit = 0;
               END IF;

               SET @credit_balance = @credit - @debit;   

               SET @credit = (SELECT SUM(amount) FROM transactions WHERE  transactions.credit = New.debit);
               SET @debit  = (SELECT SUM(amount) FROM transactions WHERE  transactions.debit = New.debit);

               IF @credit IS NULL
               THEN
                   SET @credit = 0;
               END IF;

               IF @debit IS NULL
               THEN
                   SET @debit = 0;
               END IF;

               SET @debit_balance = @credit - @debit;

               UPDATE accounts SET account_balance = @debit_balance WHERE account_no =  New.debit;
               UPDATE accounts SET account_balance = @credit_balance WHERE account_no =  New.credit;

            END

引用自：https://dba.stackexchange.com/questions/154613