当前位置：文江博客话题详情

SQL Server 触发器。需要帮助

发布于 2024-10-14 16:10:53 字数 199 浏览 4 评论 0原文

我有一个包含以下列的表格：

债务
已付
仍存

每当更新 paid 列时，我需要使用以下计算 debt 重新计算 remained 减去付费

有人可以帮助我实现这一目标吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

他夏了夏天 2024-10-21 16:10:53

您可以考虑使用计算列。

这篇文章具有从头开始创建或添加到现有架构的语法，大致如下的

ALTER TABLE yourtable ADD remainder AS debt - paid

You could consider a computed column instead.

This article has the syntax for creating from scratch or adding to an existing schema, along the lines of

ALTER TABLE yourtable ADD remainder AS debt - paid

回复收藏 0 原文

七分※倦醒 2024-10-21 16:10:53

给定表

CREATE TABLE [MyTable]
(
    MyTablePK int,
    debt numeric(10,2),
    paid numeric(10,2),
    remainder numeric(10,2)
)

以下触发器将重新计算字段 Remainder

CREATE TRIGGER tMyTable ON [MyTable] FOR INSERT, UPDATE
AS
    BEGIN
              SET NOCOUNT ON
        UPDATE mt
            Set mt.Remainder = mt.Debt - mt.Paid
            FROM [MyTable] mt INNER JOIN Inserted i
            on mt.MyTablePK = i.MyTablePK
    END

您还可以将 Remainder 定义为计算持久< /a> 列，这将具有类似的效果，而没有触发器的副作用

Given table

CREATE TABLE [MyTable]
(
    MyTablePK int,
    debt numeric(10,2),
    paid numeric(10,2),
    remainder numeric(10,2)
)

The following trigger will recalculate field Remainder

CREATE TRIGGER tMyTable ON [MyTable] FOR INSERT, UPDATE
AS
    BEGIN
              SET NOCOUNT ON
        UPDATE mt
            Set mt.Remainder = mt.Debt - mt.Paid
            FROM [MyTable] mt INNER JOIN Inserted i
            on mt.MyTablePK = i.MyTablePK
    END

You could also define Remainder as a Computed persisted column, which would have a similar effect without the side effects of triggers

回复收藏 0 原文

明明#如月 2024-10-21 16:10:53

当 SQL 可以为您执行计算时，为什么要在触发器中执行计算，并且您不必担心触发器被禁用等：

CREATE TABLE T (
    /* Other columns */
    Debt decimal (18,4) not null,
    Paid decimal (18,4) not null,
    Remained as Debt-Paid
)

这称为计算列

Why perform a calculation in a trigger when SQL can do it for you, and you don't have to worry about triggers being disabled, etc:

CREATE TABLE T (
    /* Other columns */
    Debt decimal (18,4) not null,
    Paid decimal (18,4) not null,
    Remained as Debt-Paid
)

This is called a computed column

回复收藏 0 原文

方觉久 2024-10-21 16:10:53

create trigger DebtPaid
on DebtTable
after insert, update
as if update(paid)
begin
    update DebtTable
    set remained = inserted.debt - inserted.paid
    where customerId = inserted.customerId
end

http://msdn.microsoft.com/en-us/library/ms189799.aspx

http:// benreichelt.net/blog/2005/12/13/making-a-trigger-fire-on-column-change/

create trigger DebtPaid
on DebtTable
after insert, update
as if update(paid)
begin
    update DebtTable
    set remained = inserted.debt - inserted.paid
    where customerId = inserted.customerId
end

http://msdn.microsoft.com/en-us/library/ms189799.aspx

http://benreichelt.net/blog/2005/12/13/making-a-trigger-fire-on-column-change/

回复收藏 0 原文