MySql触发器更新选择插入后的总和

我有三张桌子。

1. Members
2. Accounts
3. 交易

在将新事务插入事务表后，我想用所有 Transactions.TransactionAmount 的总和更新 Accounts.AccountBalance。

以下代码似乎对我不起作用。有什么建议么？

CREATE TRIGGER NewTrigger
    AFTER INSERT ON Transactions
    FOR EACH ROW 
    BEGIN
        UPDATE Accounts SET Accounts.AccountBalance = (
                SELECT SUM(Transactions.TransactionAmount) 
                    FROM Transactions
                    WHERE Accounts.AccountID=Transactions.AccountID
        )

Try

CREATE TRIGGER NewTrigger 
AFTER INSERT ON Transactions
FOR EACH ROW
UPDATE Accounts a
   SET a.AccountBalance = 
    (SELECT SUM(TransactionAmount) 
       FROM Transactions
      WHERE AccountID = a.AccountID)
 WHERE a.AccountID = NEW.AccountID;

Here is SQLFiddle http://sqlfiddle.com/#!2/f4e967/1 demo.

UPDATE：由于触发器不可用，请尝试换行INSERT and UPDATE进入这样的存储过程

DELIMITER $$
CREATE PROCEDURE AddTransaction(IN aid INT, amount DECIMAL(11, 2)) 
BEGIN
  START TRANSACTION;
  INSERT INTO Transactions (AccountID, TransactionAmount)
  VALUES (aid, amount);
  UPDATE Accounts a
     SET a.AccountBalance = 
      (SELECT SUM(TransactionAmount) 
         FROM Transactions
        WHERE AccountID = a.AccountID)
   WHERE a.AccountID = aid;
   COMMIT;
END $$
DELIMITER ;

然后使用它

CALL AddTransaction(1, 10.50);

Here is SQLFiddle http://sqlfiddle.com/#!2/bdf53/1该场景的演示。