CODEWITHSUNDEEP
c#.netlinq
Shaiju T
Shaiju T

Reputation: 6609

Linq Update a row if new rows are inserted in another table

I have 4 tables here, I am trying to update last entry row when new rows are added in another table using linq

CustomerTable:

CustomerId Name   EmailId
-------------------------
1          Paul   [email protected]
2          John   [email protected]

Below table I am assigning points for each LoyatyType

LoyaltyPointTable:

LoyaltyPointsId LoyaltyType     Points
---------------------------------------
1               Registration       10
2               Loginstatus         1
3               Downloading        10
4               Redemming           1
5               Sharing            20
6               Refer              10

LoyaltyDetailsTable:

 LoyaltyDetailsId LoyaltyPointsId CustomerId Dates
 -------------------------------------------------
  1                    1            1       2015-01-22 
  2                    2            1       2015-01-22 
  3                    3            2       2015-01-22
  4                    3            1       2015-01-22 
  5                    4            1       2015-01-22
  6                    4            1       2015-01-24  
  7                    2            1       2015-01-24  // here one new row is added based on LoginStatus

he has done Login one time , so his LoginStatus Point is 1 now I want update this point in another table below

Expected output:

PriceClaimTable

PriceClaimId CustomerId PriceId  Current Points

1               2         22          150
2               1         23          200 // here update the last row of CustomerId as 231 
                                      //based on every new entry on LoyaltyDetailsTable

Say if he Logins again 200 + 1 = 201
Then he is Downloading 201 + 10 = 211
Then he does sharing 211+20 = 231

can i use sql triggers in Linq and find a solution for this ?

Upvotes: 0

Views: 254

Answers (3)

jksemple
jksemple

Reputation: 257

How about creating a view on the LoyaltyDetailsTable joined to the LoyaltyPointTable that sums LoyaltyPointTable.Points across each customer e.g. something like (depending on your dialect of SQL)

CREATE VIEW PriceClaimView
AS

SELECT CustomerId, SUM(LPT.Points) AS CurrentPoints
FROM LoyaltyDetailsTable LDT INNER JOIN LoyaltyPointTable LPT ON    LDT.LoyaltyPointsId = LPT.LoyaltyPointsId
GROUP BY CustomerId

Upvotes: 1

Benny Ae
Benny Ae

Reputation: 2016

1, at your data engine

you could use a trigger

2, your program

you could code a mechanism.

Usually trihger hurt performance, i will use plan B

Upvotes: 1

karthik manchala
karthik manchala

Reputation: 13640

Use can use triggers for doing this job. You can look this for writing one.

Upvotes: 1

Related Questions