CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008-express
user3457182
user3457182

Reputation: 37

Trigger to sum in values in an SQL database column

I have a database with 2 tables in SQL Server 2008 Express.

My problem is the following: I would like to create a trigger to sum some values in first table and copy the sum into second one.

For example the first table (Head) has 5 columns :

  ID    Transaction    Acount    Date        Total_sum
  -----------------------------------------------------
   1    some text      acount1   2014.04.15   300
   2    some text      acount2   2014.04.15   500
   3    some text      acount1   2014.04.15   200

And the second table Transaction:

 HeadID    Amount    Remarks
 ----------------------------
    1       100        test1
    1       200        test2
    2       500        test3
    3       100        test3
    3       100        test4

So finally I would like to sum the values in Transaction if they have the same HeadID and copy the result into the Head table.Maybe Could I find the last ID in the 'Head' table and group HeadID-s in the 'Transaction' table and sum the values

Please help me!

Upvotes: 1

Views: 7081

Answers (1)

Suvendu Shekhar Giri
Suvendu Shekhar Giri

Reputation: 1394

From your question, I understood that you need to create a trigger on 'transaction' table. When ever a record is inserted/updated/deleted to/in/from 'transaction' table, you want the latest sum against each 'headid' to be updated to 'Head' table. If so, try the below else put some more information on your problem..

CREATE TRIGGER [dbo].[trg_Transaction]
   ON  [dbo].[Transaction]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    UPDATE dbo.Head SET Total_Sum=(SELECT SUM(Amount) FROM dbo.[Transaction] WHERE Head.Id=[Transaction].HeadId GROUP BY HeadId)
END
GO

I have assumed that you already have an entry in 'Head' before inserting/deleteing/modifying records in 'Transaction'

If I am missing something, please let me know.

Upvotes: 2

Related Questions