MySQL Trigger Issues – Null Variables During Bulk Inserts

MySQLnulltrigger

I have an SQL trigger. (mysql).

when am doing bulk inserts the summed values become null,

PS. single inserts work.bulk inserts don't.

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

So i need to figure out why that happens and fix it.

Best Answer

Looks like i needed to take care of null,

Here is what i did, though the columns might have changed

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
Related Question