触发更新整个表，即使是单行更新 [英] trigger updates entire table even on single-row update

问题描述

以下触发器旨在在locations表中更新一行时自动执行更新。更改可以一次出现一行，也可以一次出现1-n行。但是，当更新单个行时，在启用 locations_geteditdate的同时，新的时间戳将写入位置表中的所有28K行。我知道我在这里缺少明显的东西，感谢您的帮助。

The following triggers are meant to automate updates when a row is updated in a locations table. Changes can occur one row at a time, or 1-n many rows at a time. However, when updating a single row, while the "locations_geteditdate" is enabled, a new time stamp is written to all 28K rows in the locations table. I know I'm missing something obvious here, thanks for the help.

ALTER TRIGGER   [dbo].[locations_geteditdate]
ON  [dbo].[TBL_LOCATIONS]   
instead of update
AS
begin
declare @recs INT
select @recs = COUNT(*)
from dbo.TBL_LOCATIONS a
join inserted i on i.Location_ID = a.Location_ID
if @recs > 0
update dbo.TBL_LOCATIONS
SET EditDate = GETDATE()
end
GO

alter TRIGGER   [dbo].[locations_move_topo]
ON  [dbo].[TBL_LOCATIONS]
for update   
AS   
BEGIN   
  update dbo.TBL_LOCATIONS
  set topo_name = dbo.TLU_TOPO_BOUNDS.name
  FROM  dbo.TBL_LOCATIONS
      inner join dbo.TLU_TOPO_BOUNDS
      on dbo.TBL_LOCATIONS.Location_ID = dbo.TBL_LOCATIONS.Location_ID
      where (TLU_TOPO_BOUNDS.Shape.STContains(TBL_LOCATIONS.SHAPE) = 1) ; 
END

接受的答案：

alter TRIGGER   [dbo].[locations_geteditdate]
ON  [dbo].[TBL_LOCATIONS]   
for update 
as
begin
update dbo.TBL_LOCATIONS
SET EditDate = GETDATE()
from dbo.TBL_LOCATIONS locn
inner join inserted i on i.location_id = locn.Location_ID
end
GO

推荐答案

在您的if条件下（在 locations_geteditdate ），您没有where子句；因此，它包括所有记录：

In your if condition (in locations_geteditdate) you have no where clause; therefore it is including all records:

if @recs > 0
update dbo.TBL_LOCATIONS
SET EditDate = GETDATE()
WHERE ???
end

您已正确使用插入了 表以查看已更新的内容，但仅用于识别记录计数

You correctly used the inserted table to see what had been updated but only to identify a record count

因此，读取您放入触发器中的代码后，看起来就像是尝试做的只是在表上加上一个时间戳，以表明它已被更新。

So reading the code you've put in the trigger, it looks like what you're trying to do is just apply a timestamp to the table to show that when it has been updated.

为此，您至少有以下选择：

1.如果您实际上不需要在那里的可识别日期时间，则可以使用时间戳字段代替日期时间，并自动更新它。

2.如果您可以控制在何处执行更新，表格中，您只需在其中设置 EditDate （即在存储过程中）

You have at least these options for this:
1. If you don't actually need a recognisable datetime in there you can use a timestamp field instead of a datetime and get it automatically updated.
2. If you can control where updates are performed to the table you can just set EditDate there (i.e. in stored procedures)

但是，假设您想要可识别的日期时间和您无法控制对表进行更新的位置，这就是为什么要实现触发器而不是仅具有proc集合 EditDate 的原因，您需要选择两种类型之一触发器：

However, assuming that you want a recognisable datetime and you can't control where updates to the table are happening which is why you're implementing a trigger rather than just have a proc set EditDate, you need to go forward with one of the two types of trigger:

A）因此，如果您坚持使用代替触发器，则需要了解它替换了发生了因此，您有责任去做它要去做的工作。您逐列检查已更改的内容：

例如

A) So if you persist with an "instead of" trigger you need to understand that it replaces the update that would have happened. So its incumbent upon you to then do the work that it was going to. You check column by column what has changed:
e.g.

IF UPDATE (price)
BEGIN 
     UPDATE t
     SET price = i.price
     FROM TBL_LOCATIONS t join inserted i
          ON i.locn_id = t.locn_id
END

...对每列重复一次（如果有意义，可以合并更新）

... repeat for each column (you can merge the updates if it makes sense)

B）或者，您可以更改为之后触发器，允许进行更新（因此，您不必逐列编写代码来检查已更新的内容）但是您必须然后进行检查 EditDate 列，并且如果 EditDate 列已更改，则不执行更新。如果您不这样做，则会陷入无限循环-您的proc会调用触发器，而触发器会触发触发器等

B) Alternatively you can change to an "after" trigger, allow the update to happen (so you don't have to code column by column to check what's been updated) BUT YOU MUST then have a check on the EditDate column and NOT perform an update if its the EditDate column that has changed. If you don't do this you'll be in an infinite loop - your proc calls the trigger which calls the trigger etc

即

IF NOT UPDATE(EditDate)
BEGIN
   UPDATE dbo.TBL_LOCATIONS
   SET EditDate = GETDATE()
   FROM dbo.TBL_LOCATIONS locn
     INNER JOIN inserted i on i.locn_id = locn.locn_id
END

这篇关于触发更新整个表，即使是单行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！