How to update a single table using trigger in MS SQL 2008

Question

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

Answer 1

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

Answer 2

Create two triggers, an INSTEAD OF UPDATE and INSTEAD OF INSERT. Here is the code for the INSTEAD OF UPDATE:

CREATE TRIGGER dbo.UpdatePD ON PeroidicDedcution 
INSTEAD OF Update
AS
SET NOCOUNT ON

UPDATE p
    SET col1=i.col1
       ,col2=i.col2
    FROM INSERTED  i
        INNER JOIN PeroidicDedcution p ON i.PK=p.PK

UPDATE PeroidicDedcution 
    SET REmaininAmoubnt=(SELECT TotalDeduction-(SELECT SUM(Paid) FROM PeroidicDeduction) FROM PeroidicDeduction)
go

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:

CREATE TRIGGER dbo.InsertPD ON PeroidicDedcution 
INSTEAD OF INSERT
AS
SET NOCOUNT ON

INSERT INTO PeroidicDedcution
    (col1, col2, ...)
    SELECT
       col1, col2, ...
    FROM INSERTED

UPDATE PeroidicDedcution 
    SET REmaininAmoubnt=(SELECT TotalDeduction-(SELECT SUM(Paid) FROM PeroidicDeduction) FROM PeroidicDeduction)
go