SQL Server触发表中的更新数据，但仅在客户进行插入或更新一个客户端时才更新数据 [英] SQL Server trigger update data in to table but should update data only when customer doing insert or update one client

问题描述

现在触发器看起来像这样，并且部分起作用

Right now trigger looks like this and it works partly

我的触发器看起来像这样

My trigger looks like this

   ALTER  trigger [dbo].[tr_EligebilityCheck]
on [dbo].[Clients]
 for INSERT,update
as 

BEGIN
UPDATE Clients 
SET 
StatusID = 5
WHERE 
ClientID IN (Select ClientID
            from Clients c 
            join inserted --ADD THIS JOIN
            on inserted.ClientID = c.ClientID
            join IncomeEligibility i 
            on c.HshldSize = i.HshldSize
            where StatusID in (1,2) 
            and  WIC = 0
            and (c.CategCode = 'SR' 
                and ((MonthlyYearly = 'month' and c.AnnualHshldIncome >= i.SeniorMo) 
                or (c.AnnualHshldIncome >= i.SeniorYr and MonthlyYearly ='year'))
                and DATEDIFF(YEAR,DOB,GETDATE()) < 60)
            or
                (c.CategCode = 'CH' 
                and ((MonthlyYearly = 'month' and c.AnnualHshldIncome >= i.WomanChildMo) 
    or (c.AnnualHshldIncome >= i.WomanChildYr and MonthlyYearly ='year'))
                and DATEDIFF(YEAR,DOB,GETDATE()) > 6))

update Clients
set StatusID = 4 
where WIC =1
from Clients --ADD THIS FROM STATEMENT
join inserted --ADD THIS JOIN
on inserted.ClientID = Clients.ClientID

END

当我使用CategCode ='SR'插入客户时，它仅检查DOB并触发客户年龄小于60岁的客户，但如果客户年龄较大，则不检查此客户

when I inset client with CategCode = 'SR' it check only DOB and fire if client is younger than 60 but if clients is older it did not check this

and ((MonthlyYearly = 'month' and c.AnnualHshldIncome >= i.SeniorMo) 
                    or (c.AnnualHshldIncome >= i.SeniorYr and MonthlyYearly ='year'))

如果我插入的客户的CategCode ='CH'，则检查收入，但不检查DOB。

If I insert client with CategCode = 'CH' it check Income but did not check DOB.

推荐答案

只要您对主键有正确的引用，我就看不到为什么您需要 INSTEAD OF 触发器。看来在任何情况下都不会阻止插入或更新，对吗？您只需要确保StatusID的值即可。

As long as you have a proper reference to a primary key, I don't see why you would need an INSTEAD OF trigger. It doesn't seem like there is any situation in which you'd prevent an insert or update, correct? You just want to make sure of the value of StatusID. There's no reason that has to be done before the update.

我相信更新太多行的原因是，您并没有将触发器限制为仅已插入表中的那些行。尝试将联接添加到触发器，如下所示：

I believe the reason you're getting too many rows updated is that you're not limiting the trigger to only those rows in the inserted table. Try adding a join to your trigger, like so:

ALTER  trigger [dbo].[tr_EligebilityCheck]
on [dbo].[Clients]
 for INSERT,update
as 

BEGIN
UPDATE Clients 
SET 
StatusID = 5
WHERE 
ClientID IN (Select ClientID
            from Clients c 
            join inserted --ADD THIS JOIN
            on inserted.ClientID = c.ClientID
            join IncomeEligibility i 
            on c.HshldSize = i.HshldSize
            where StatusID in (1,2) 
            and  WIC = 0
            and (c.CategCode = 'SR' 
                and ((MonthlyYearly = 'month' and c.AnnualHshldIncome >= i.SeniorMo) 
                or (c.AnnualHshldIncome >= i.SeniorYr and MonthlyYearly ='year'))
                and DATEDIFF(YEAR,DOB,GETDATE()) < 60)
            or
                (c.CategCode = 'CH' 
                and ((MonthlyYearly = 'month' and c.AnnualHshldIncome >= i.WomanChildMo) 
    or (c.AnnualHshldIncome >= i.WomanChildYr and MonthlyYearly ='year'))
                and DATEDIFF(YEAR,DOB,GETDATE()) > 6))

update Clients
set StatusID = 4 
where WIC =1
from Clients --ADD THIS FROM STATEMENT
join inserted --ADD THIS JOIN
on inserted.ClientID = Clients.ClientID

END

如果您确实想使用 INSTEAD OF 触发器，则需要指出以下几点： INSTEAD OF 是不是与更新前相同。 更新前更改插入的表，然后继续进行更新。 INSTEAD OF 会完全取消插入或更新，这意味着您需要明确地重写它。我下面有一个例子。

If you do want to use an INSTEAD OF trigger, a few pointers on that: INSTEAD OF is not the same thing as BEFORE UPDATE. BEFORE UPDATE alters the inserted table and then proceeds with the update. INSTEAD OF cancels the insert or update entirely, which means you need to explicitly re-write it. I have an example below.

此外，如果要使用 INSTEAD OF 触发器，则需要分别使用 INSERT 和 UPDATE 触发器，否则您需要将查询写为 MERGE 声明。在下面的示例中，我将使用 INSERT ：

Also, if you want to use an INSTEAD OF trigger, you'll either need separate INSERT and UPDATE triggers, or you'll need to write your query as a MERGE statement. I'll use INSERT in my example below:

ALTER  trigger [dbo].[tr_EligebilityCheck]
on [dbo].[Clients]
INSTEAD OF INSERT
as 

BEGIN

--First, set StatusID in the inserted table
UPDATE inserted
SET 
StatusID = 5
WHERE 
ClientID IN (Select ClientID
            from Clients c 
            join IncomeEligibility i 
            on c.HshldSize = i.HshldSize
            where StatusID in (1,2) 
            and  WIC = 0
            and (c.CategCode = 'SR' 
                and ((MonthlyYearly = 'month' and c.AnnualHshldIncome >= i.SeniorMo) 
                or (c.AnnualHshldIncome >= i.SeniorYr and MonthlyYearly ='year'))
                and DATEDIFF(YEAR,DOB,GETDATE()) < 60)
            or
                (c.CategCode = 'CH' 
                and ((MonthlyYearly = 'month' and c.AnnualHshldIncome >= i.WomanChildMo) 
    or (c.AnnualHshldIncome >= i.WomanChildYr and MonthlyYearly ='year'))
                and DATEDIFF(YEAR,DOB,GETDATE()) > 6));

update inserted
set StatusID = 4 
where WIC =1;

--Once the inserted table looks right, proceed with the insert
--You need to explicitly write an insert statement, or nothing will happen
INSERT INTO [dbo].[Clients]
  <column_list>
SELECT <column_list>
FROM inserted;

这篇关于SQL Server触发表中的更新数据，但仅在客户进行插入或更新一个客户端时才更新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！