SQL如何从另一个表求和并插入另一个表

小编典典

SQL如何从另一个表求和并插入另一个表

sql

现在我在Delphi应用程序中求和，然后将其插入SQL数据库中，
但我希望数据库在我插入新数据库时求和并自动插入，ExpenseAmount
我有2个表

ProjectsTable (ProjectID, ProjectName, Cost, ExpenseTotal, CostRemaining)
ExpenseTable (ID, ProjectID, ExpenseAmount, ExpenseDate)

我想ExpenseAmount为每个项目自动求和ProjectsTable并ExpenseTotal从中插入它，ProjectsTable
我想SQL全部Delphi

阅读 218

2021-04-15

共1个答案

小编典典

将此触发器添加到您的ExpenseTable

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
    UPDATE ProjectsTable P
    SET ExpenseTotal = 
    (SELECT SUM(ExpenseAmount) from ExpenseTable
    where ExpenseTable.ProjectID= P.ProjectID)
    where P.ProjectID = New.ProjectID;
END

不要忘记添加触发器 After Update 并 After Delete 更新ExpenseTotal

2021-04-15