SQL Server Trigger. Need Help

I have a table with these columns:

• debt
• paid
• remained

W开发者_如何学Pythonhenever the paid column is updated I need to recalculate the remained using the following calculation debt minus paid

Could someone help me achieve this?

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

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

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

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/

Computed columns can be good but they are calculated on the fly and arent stored anywhere, for some big queries that perform long calculations having a physical denormalyzed value in Remained controlled by trigger can be better than computed columns.

In your trigger remember to only update rows that were updated , you access those by virtual table Inserted Deleted available in triggers.