插入后，使用两列主键更新时间戳触发器 [英] After insert, update timestamp trigger with two column primary key

问题描述

我有一个简单的详细信息表，如下所示：

I have a simple details table like so:

listid
custid
status
last_changed

主键包含两个 listid 和 custid 。

现在，我正在尝试设置用于设置的触发器每次插入或更新时，last_changed 列都显示为当前日期时间。我已经找到了很多有关如何使用单个PK列执行此操作的信息，但是使用多个PK时，如何正确地从INSERTED表中指定PK变得令人困惑。

Now I'm trying to setup a trigger that sets the last_changed column to the current datetime every time an insert or update happens. I've found lots of info on how to do that with a single PK column, but with multiple PKs it gets confusing on how to correctly specify the PKs from the INSERTED table.

触发器必须在SQL Server 2005/2008 / R2中工作。

The trigger has to work in SQL Server 2005/2008/R2.

感谢您使用有效的触发器代码！

Thanks for a working trigger code!

在这种情况下，奖金还会检查数据是否确实被更改并且仅更新last_changed，但是为了真正理解如何正确编码主要问题，我想将其视为单独的代码块，如果在

Bonus would be to also check if the data was actually altered and only update last_changed in that case but for the sake of actually understanding how to correctly code the main question I'd like to see this as a separate code block if at all.

推荐答案

Hmm ....仅仅因为主键是由两列组成的，实际上并不需要太大差异...。

Hmm.... just because the primary key is made up of two columns shouldn't really make a big difference....

CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE 
AS
  UPDATE dbo.YourTable
  SET last_changed = GETDATE()
  FROM Inserted i
  WHERE dbo.YourTable.listid = i.listid AND dbo.YourTable.custid = i.custid

您只需要在两个表（您自己的数据表和 Inserted 伪表）之间建立JOIN这两列...

You just need to establish the JOIN between the two tables (your own data table and the Inserted pseudo table) on both columns...

我错过了什么吗？ .....

Are am I missing something?? .....

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