MySQL触发器更新选择插入后的总和 [英] MySql Trigger Update select sum after insert

问题描述

我有三个桌子.

1. 会员
2. 帐户
3. 交易

在向交易表中插入新交易后，我想用所有交易的总和来更新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
        )

解决方案

尝试

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;

这里是 SQLFiddle 演示.

更新:由于触发器不可用，因此请尝试将INSERT和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);

这是该情况下的 SQLFiddle 演示./p>

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
        )

解决方案

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.

这篇关于MySQL触发器更新选择插入后的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！