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

问题描述

我正在创建After Insert触发器，它可以正常工作，但是在触发器内部执行语句之前我有一定条件

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

1. 基于不同的CustomerId运行触发器，我想检查在我的LoyaltyDetailsTable中插入了哪个CustomerId，说如果最后一次插入
   是Customerid = 2，则在该条件下传递该Customerid，然后运行
   触发器，或者如果Customerid = 1，然后为该ID运行
   的触发器，依此类推。


2. 我要检查PriceClaimTable中插入的CustomerId是否存在，如果存在，则更新详细信息，否则只需将
   值仅插入LoyaltyDetailsTable。

触发查询

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.

任何帮助都很好。

推荐答案

在这里寻找的是插入的表。每次发出 UPDATE 语句时，SQL Server都会生成两个虚拟表，分别称为 insertted 和 deleted 存储有关您正在进行的数据修改的信息。您可以通过触发器访问这些表。有关更多信息，请参见此处： https://msdn.microsoft.com/zh- us / library / ms191300.aspx

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

您可以使用插入的来获取您的ID寻找。因此，而不是：

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

WHERE ClaimCustomerId=1

您可以使用：

WHERE ClaimCustomerId=inserted.ClaimCustomerId

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