Sql Trigger After Insert Update another table with conditions

Question

I am creating After Insert trigger , its working fine, but I have certain conditions before executing the statements inside the trigger

Based on Different CustomerId Run the trigger, I want check which CustomerId got inserted in my LoyaltyDetailsTable, say if last insert was Customerid=2 then pass that Customerid in where condition then run the trigger , or if Customerid = 1 then run the trigger for that Id, so on.

I want to check whether in PriceClaimTable the inserted CustomerId exist or not, If exists then update the details else just insert the values in LoyaltyDetailsTable only.

Trigger query

CREATE TRIGGER DetailsAfterInsert ON [dbo].[LoyaltyDetailsTable]
FOR INSERT

as

UPDATE PriceClaimTable 
SET CurrentPoints = 
(  
(SELECT SUM(LoayaltyPointsTable.Points) AS RecentPoints FROM LoayaltyPointsTable
join LoyaltyDetailsTable ON LoayaltyPointsTable.LoyaltyPointsId 
= LoyaltyDetailsTable.LoyaltyPointsId
WHERE CustomerId=1 and LoyaltyDetailsId= (SELECT MAX(LoyaltyDetailsId)  
AS LoyaltyDetailsTable FROM LoyaltyDetailsTable))

+ 

(SELECT CurrentPoints FROM PriceClaimTable WHERE ClaimCustomerId=1 and 
PriceClaimId=(SELECT max(PriceClaimId) FROM PriceClaimTable
))

) 
WHERE ClaimCustomerId=1 and PriceClaimId=(SELECT max(PriceClaimId) FROM PriceClaimTable)

This is my first attempt to write a trigger, and here is table structure.

Any help would be great.

Two things. First your trigger is not referencing the inserted virtual table. Second, this seems like a computed column would make a lot more sense than a trigger. You will never have accurate data when you try to maintain a balance like this. At some point it will get out of synch. — Sean Lange
– Sean Lange, Commented Feb 11, 2015 at 15:10

Jeff Rosenberg · Accepted Answer · 2015-02-11 15:10:03Z

2

What you're looking for here is the inserted table. Every time you issue an UPDATE statement, SQL Server generates two virtual tables called inserted and deleted that store information on the data modifications you're making. These tables are accessible from your trigger. For more information, see here: https://msdn.microsoft.com/en-us/library/ms191300.aspx

You can use inserted to get the IDs you're looking for. So, instead of:

WHERE ClaimCustomerId=1

you can use:

WHERE ClaimCustomerId=inserted.ClaimCustomerId

answered Feb 11, 2015 at 15:10

Jeff Rosenberg

3,5821 gold badge21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shaiju T Over a year ago

hi, that was good idea, from here, i changed to WHERE CustomerId=(select CustomerId from INSERTED ) , but i get error as'Invalid object name 'INSERTED'.'

Shaiju T Over a year ago

Thank you so much, for guiding me, as you said i was looking for that one word 'inserted', and that made the solution, the error :'Invalid object name 'INSERTED'.' was resolved when i came to know that inserted runs only when insert happens in a table. i managed to execute all correctly, sometimes your one word trains us here in stackoverflow, so dont stop answering if you have just small words which can be turned into solutions. Appreciate your effort.

Collectives™ on Stack Overflow

Sql Trigger After Insert Update another table with conditions

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related