MySql Trigger Update select sum after insert

Question

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
        )

Solution

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.

Comments

[Marcus Reed]

No luck. Error SQL query: CREATE TRIGGER NewTrigger AFTER INSERT ON Transactions FOR EACH ROW BEGIN UPDATE Accounts a SET a.AccountBalance = ( SELECT SUM( TransactionAmount ) FROM Transactions WHERE AccountID = a.AccountID ) WHERE a.AccountID = NEW.AccountID MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 9

[peterm]

@MarcusReed There is no need for BEGIN. See updated answer and sqlfiddle demo.

[Marcus Reed]

MySQL said: #1227 - Access denied; you need the SUPER privilege for this operation

[peterm]

@MarcusReed In MySQL 5.0 CREATE TRIGGER requires the SUPER privilege. Therefore either grant needed privileges for your current user under which you're creating a trigger, or do it under superuser (usually root) account

[Marcus Reed]

I am using godaddy phpmyadmin and I cant change the privileges. Is there any other way of updating that field with the sum data?

[peterm]

@MarcusReed GoDaddy and other providers restrict creating triggers to shared hosting users. See support.godaddy.com/help/article/2400/…. You can simply execute UPDATE after INSERT on Transactions or better yet wrap it up in a stored procedure. (You should be able to create an SP in your GO Daddy account)

[peterm]

@MarcusReed See updated answer for a scenario with a stored procedure.