Yg1GZnsZ

1DROP TRIGGER IF EXISTS  `calculate_balances`;
2
3
4CREATE TABLE  IF NOT EXISTS temp(account_no VARCHAR(60),amount DECIMAL  (10,2));
5
6CREATE TRIGGER `calculate_balances` AFTER INSERT ON `transactions` FOR EACH ROW
7  BEGIN
8
9SET @credit = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_to = New.account_to);
10SET @debit  = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_from = New.account_to);
11SET @credit_balance = @credit - @debit;
12
13SET @credit2 = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_to = New.account_from);
14SET @debit2  = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_from = New.account_from);
15SET @debit_balance = @credit2 - @debit2;
16
17#     UPDATE `accounts` SET account_balance = @debit_balance WHERE account_no =  New.account_from;
18#     UPDATE `accounts` SET account_balance = @credit_balance WHERE account_no =  New.account_to;
19
20INSERT INTO temp VALUES(New.account_from,@debit_balance);
21INSERT INTO temp VALUES(New.account_to,@credit_balance);
22
23
24
25END