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

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

---

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

---

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