如何在 MS SQL 2008 中使用触发器更新单个表
我有一个表PeroidicDeduction,字段是ID(自动递增),TotalDeduction(egit可以贷款),Paid(每个月的扣除额),RemainingAmount,
我想要的是每次插入或更新表时---RemainingAmount 将获取 TotalDeduction-SUM(Paid) 的值...并写入以下触发器...但对我不起作用
CREATE TRIGGER dbo.UpdatePD ON 定期扣除 插入后,更新
开始
扣除
更新周期性 SET REmaininAmoubnt=(SELECT TotalDeduction-(SELECT SUM(Paid) FROM PeroidicDeduction) FROM PeroidicDeduction)
END
注意:它位于单个表上
I have a table PeroidicDeduction and the fields are ID(auto-increment),TotalDeduction(e.g.it can be loan),Paid(on which the deduction for each month),RemainingAmount,
What I want is when every time I insert or update the table---RemainingAmount will get the value of TotalDeduction-SUM(Paid)....and writ the following trigger...but dosen't work for me
CREATE TRIGGER dbo.UpdatePD
ON PeroidicDedcution
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE PeroidicDedcution
SET REmaininAmoubnt=(SELECT TotalDeduction-(SELECT SUM(Paid) FROM PeroidicDeduction) FROM PeroidicDeduction)
END
NOTE: it is on a Single table
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
创建两个触发器,一个 INSTEAD OF UPDATE 和 INSTEAD OF INSERT。以下是 INSTEAD OF UPDATE 的代码:
它将执行触发触发器的原始更新,以及问题中触发器的 SUM 逻辑。
这是 INSTEAD OF INSERT 触发器:
Create two triggers, an INSTEAD OF UPDATE and INSTEAD OF INSERT. Here is the code for the INSTEAD OF UPDATE:
It will do both the original update that fires the trigger, as well as the SUM logic from the trigger in the question.
here is the INSTEAD OF INSERT trigger:
您不应该使用触发器来执行此操作,因为如果您在表上有更新触发器并且触发器导致并更新到同一个表,它将递归地自行触发。而是将逻辑添加到插入和更新存储过程中
You should not use a trigger to do this as it will recursively fire itself if you have an update trigger on a table and the trigger causes and update to the same table. rather add the logic to your insert and update stored procedures