MySql Trigger Update select sum after insert

问题:

I have three tables.

1. Members
2. Accounts
3. Transactions

I want to update Accounts.AccountBalance with the sum of all Transactions.TransactionAmount after a new transaction is inserted into the Transactions table.

The following code does not seem to work for me. Any suggestions?

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
        )

回答1:

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 demo.

UPDATE: Since triggers are not available to you try wrap INSERT and UPDATE into a stored procedure like this

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 ;

And then use it

CALL AddTransaction(1, 10.50);

Here is SQLFiddle demo for that scenario.