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]

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?

回答1:

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

Try this:

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