插入后的Sql触发器用条件更新另一个表 [英] Sql Trigger After Insert Update another table with conditions

问题描述

我正在创建 After Insert 触发器，它工作正常，但在执行触发器内的语句之前我有某些条件

<块引用>

1. 基于不同的 CustomerId 运行触发器，我想检查哪个 CustomerId 被插入到我的 LoyaltyDetailsTable 中，比如上次插入是 Customerid=2 然后在 where 条件中传递那个 Customerid 然后运行触发器，或者如果 Customerid = 1 然后运行该 ID 的触发器，
2. 我想检查在 PriceClaimTable 中插入的 CustomerId 是否存在，如果存在则更新详细信息，否则只需插入仅 LoyaltyDetailsTable 中的值.

触发查询

CREATE TRIGGER DetailsAfterInsert ON [dbo].[LoyaltyDetailsTable]插入作为更新价格索赔表SET CurrentPoints =((SELECT SUM(LoayaltyPointsTable.Points) 作为最近点数从 LoayaltyPointsTable在 LoyaltyPointsTable.LoyaltyPointsId 上加入 LoyaltyDetailsTable= LoyaltyDetailsTable.LoyaltyPointsIdWHERE CustomerId=1 and LoyaltyDetailsId= (SELECT MAX(LoyaltyDetailsId)AS LoyaltyDetailsTable FROM LoyaltyDetailsTable))+(SELECT CurrentPoints FROM PriceClaimTable WHERE ClaimCustomerId=1 和PriceClaimId=(SELECT max(PriceClaimId) FROM PriceClaimTable)))WHERE ClaimCustomerId=1 和 PriceClaimId=(SELECT max(PriceClaimId) FROM PriceClaimTable)

这是我第一次尝试编写触发器，这里 是表结构.

任何帮助都会很棒.

解决方案

您在此处寻找的是 inserted 表.每次发出 UPDATE 语句时，SQL Server 都会生成两个名为 inserted 和 deleted 的虚拟表，用于存储有关您正在进行的数据修改的信息.可以从触发器访问这些表.有关详细信息，请参阅此处:https://msdn.microsoft.com/en-我们/图书馆/ms191300.aspx

您可以使用 inserted 来获取您要查找的 ID.所以，而不是:

WHERE ClaimCustomerId=1

你可以使用:

WHERE ClaimCustomerId=inserted.ClaimCustomerId

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.

解决方案

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

这篇关于插入后的Sql触发器用条件更新另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！