使用表触发器更新插入的行 [英] Updating inserted row with table trigger

问题描述

我不太清楚如何使用表触发器更新最后插入的行。

I can't quite figure out how to update the last inserted row using table triggers.

我尝试过：

I tried this:

create TRIGGER [dbo].[t_checkAucEquFields]<br />
ON [dbo].[_aucEquUpdateLog]
AFTER INSERT
as
update a set inserted.[Status] = coalesce(pes.id,'22')
from [_aucEquUpdateLog] a
left join v_pobEquStatus pes on pes.statusDescr = inserted.[Status]

<我希望将insert.Status的文本替换为整数pes.id，例如 In stock。如果无法进行连接，我希望它的默认值为22。

I would like the text of inserted.Status - ex being "In stock" to be replaced with the integer pes.id. If the join cannot be made, I would like it to default to value 22.

尝试上述查询时出现此错误：

I get this error when trying above query:

Msg 4104, Level 16, State 1, Procedure t_checkAucEquFields, Line 7
The multi-part identifier "inserted.Status" could not be bound.

我可能离我们很远，但是我却迷失了如何正确使用它的方法？

I might be way off, but I am quite lost as of how to do it in a proper way?

推荐答案

您需要加入插入的表，并更新实际的表而不是插入的表

You need to join the inserted table, and update the actual table instead of the inserted table

尝试以下操作：

CREATE TRIGGER [dbo].[t_checkAucEquFields]
ON [dbo].[_aucEquUpdateLog]
AFTER INSERT
as
begin
UPDATE a 
SET 
  [Status] = coalesce(pes.id,'22')
FROM 
  [_aucEquUpdateLog] a
JOIN
  inserted i 
ON 
  i.[your primary key] = a.[your primary key]
LEFT JOIN
  v_pobEquStatus pes 
ON 
  pes.statusDescr = i.[Status]
end

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