更新时的 Sql 服务器设置当前时间戳 [英] Sql server on update set current timestamp

问题描述

我需要一个时间戳字段，每次用户修改记录时都会更新.

I need a timestamp field which updates every time the user modifies the record.

到目前为止，我使用了 MySql，我什至可以在字段创建中使用它:

So far I used MySql in which I can even use this in the field creation:

Alter table myTable add `last_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP **ON UPDATE CURRENT_TIMESTAMP**

我在 Sql Server 中找不到这种可能性.

I couldn't find this possibility in Sql Server.

然后我尝试编写一个触发器.

Then I tried writing a trigger.

在 MySql 触发器中，这很简单:

In a MySql trigger this is simple :

Set new.last_time=CURRENT_TIMESTAMP();

Sql Server 似乎既不知道新语法，也不知道旧语法，它在编译时给了我错误.

Sql Server doesn't seem to know neither new, nor old syntax, it gave me error on compilation.

:

update myTable set last_time =CURRENT_TIMESTAMP;

有效，但它更新了所有行而不是当前行.

worked, but it updated all the rows instead of the current.

有没有办法告诉Sql Server更新当前记录?我应该使用update where myid=something"吗?

Isn't there a way the tell Sql Server to update the current record? Should I user "update where myid=something" ?

SQL Server 不知道它正在处理的实际记录是什么吗?

Doesn't SQL Server know which is the actual record it is processing?

推荐答案

如果你真的需要一个时间戳 - 然后在插入和更新时创建一个触发器，用当前的时间戳更新列.

And if you really need a timestamp - then make a trigger on insert and update that updates the column with the current timestmap.

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

要更新单行(已编辑或插入)，您应该使用

To update a single row (which has been edited or inserted) you should use

CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE 
AS
  UPDATE f set LastUpdate=GETDATE() 
  FROM 
  dbo.[YourTable] AS f 
  INNER JOIN inserted 
  AS i 
  ON f.rowID = i.rowID;

这些应该就是你所需要的.GETUTCDATE() 如果你想要它在 UTC(我更喜欢)

These should be all you need. GETUTCDATE() if you want it in UTC (which I prefer)

SQL Server 绝对知道它处理的行

SQL Server absolutely knows the rows it processes

update myTable set last_time =CURRENT_TIMESTAMP ;工作，但它更新了所有行而不是当前行.

update myTable set last_time =CURRENT_TIMESTAMP ; worked, but it updated all the rows instead of the current.

是的，猜猜看 - 因为这正是您告诉 SQL Server 的内容:更新表中的所有行.

Yeah, guess what - because that is exactly what you tell SQL Server: Update all rows in the table.

Sql Server 不知道它正在处理的实际记录是哪条吗?

Doesn't Sql Server know which is the actual record it is processing?

集合没有当前行;) 这就是问题开始的地方.

Sets have no current row ;) That is where the problem starts.

完全按照您的意愿执行此操作的唯一方法是在我开头的回答中:时间戳.不过，由于存在误解，我添加了一个建议:买一本关于 SQL 基础知识的书.

The only way to do that exactly as you want is up in my answer on the beginning: a timestamp. Due to the misconceptions, though, I add an advice: get a book about SQL basics.

这篇关于更新时的 Sql 服务器设置当前时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！