Sql Trigger After Insert Update another table with

问题:

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

1. 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.
2. 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.

回答1:

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