CODEWITHSUNDEEP
sql-servertriggers
Irakli Lekishvili
Irakli Lekishvili

Reputation: 34158

SQL Server Trigger. Need Help

I have a table with these columns:

Whenever the paid column is updated I need to recalculate the remained using the following calculation debt minus paid

Could someone help me achieve this?

Upvotes: 1

Views: 220

Answers (5)

Valentin Kuzub
Valentin Kuzub

Reputation: 12073

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.

Upvotes: 1

StuartLC
StuartLC

Reputation: 107237

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

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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

Upvotes: 3

Kalle
Kalle

Reputation: 2293

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/

Upvotes: 2

richaux
richaux

Reputation: 2672

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

Upvotes: 4

Related Questions