如何使用触发器更新另一个表 [英] How to use a trigger to update another table

问题描述

tblBudget（butID，butName，butAmount，IncurredTD_Amount）

tblInvoice（ID，ItemName，InvoiceAmount，butID）

这里是我的两张表，当我更新发票时表格我将在发票上的任何插入或更新中触发启动，这将减少预算金额intblbutget并使用butID作为tblInvoice中的参考（butAmount-InvoiceAmount和IncurredTDAmount + INvoiceAmount）自动增加INcurreTDAmount ... ...这可能吗？如果有的话，性能问题是什么？怎么办呢.....提前一百万感谢



我尝试了什么：

tblBudget(butID,butName,butAmount,IncurredTD_Amount)
tblInvoice(ID,ItemName,InvoiceAmount,butID)
here are my two tables, when i update the invoice table i would have a trigger kick in on any insert or update on invoice, that will reduced the budget amount intblbutget and also increase the INcurreTDAmount auomatically using the butID as reference in the tblInvoice(butAmount-InvoiceAmount,and IncurredTDAmount+INvoiceAmount)......is this possible? what are the performance issues if at all any? how can it be done.....thanks a million in advance

What I have tried:

AFTER INSERT,  UPDATE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @Inv_Amount float,@Bud_ID int
 
       SELECT @Bud_ID = INSERTED.Bud_ID       
       FROM INSERTED
 
       INSERT INTO dbo.tblBudget(Bud_Amount,IncurTD_Amount,ID) IncurTD_Amount
       VALUES(Bud_Amount - @Inv_Amount, IncurTD_Amount + @Inv_Amount,'Inserted')
END

但是我收到一个错误：

推荐答案

CREATE TRIGGER TR_Budget ON [dbo].[tblInvoice] 
	FOR INSERT, UPDATE
	AS
	BEGIN
          -- your code here, update tblBudget
	END

其抱怨关于插入声明中不匹配的coloumn名称的ninig：

its complaninig about the coloumn name that dont match in the insert statement:

Msg 207, Level 16, State 1, Line 1

这是不可能的，因为我正在引用正确的表，但我想我不太熟悉SQL。也许临时表会做什么？不知道.....

which is not possible as the i am referencing the right table but i guess i am not too well versed with SQL. Maybe a temporary table will do? dont know.....

这篇关于如何使用触发器更新另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！