更新时的 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屋!