更新时间:2023-02-04 23:22:42
尝试
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.
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.