Sql Server（实体框架）：created_at，updated_at列 [英] Sql Server (Entity Framework): created_at , updated_at Columns

问题描述

我想我应该提到我试图让Entity Framework\SQL服务器做一些我从Rails来的东西。我真的只想知道最好的方式来自动创建_ updated_at列值，用于在数据库中插入/更新的记录。

现在我已经连接到 ObjectContext.SavingChanges 事件，事情运行正常。在我写了并测试了我的代码之后，我意识到可能会有更好或更快的方法来实现。

解决方案

是使用insert / update触发器将created_at和updated_at列设置为当前时间。

插入触发器将如下所示：

  CREATE TRIGGER [dbo]。[inserted_myTable] ON [dbo]。[myTable] AFTER INSERT 
 AS 
 BEGIN 
 SET NOCOUNT ON; 
更新[dbo]。[myTable]设置AdmitDateTime = GetDate（）其中pkid in（SELECT pkid FROM Inserted）
 END 
  

更新触发器将如下所示：

  CREATE TRIGGER [dbo]。[updated_myTable] ON [dbo]。[myTable]更新后
 AS 
 BEGIN 
 SET NOCOUNT ON; 
更新[dbo]。[myTable]设置AdmitDateTime = GetDate（）其中pkid in（SELECT pkid FROM Inserted）
 END 
  

触发方法的一个优点是时间/日期将始终处于相同的时区。另一个优点是，如果有人在应用程序之外修改数据库记录，则字段仍然更新。

I think I should mention I'm trying to get Entity Framework\SQL server to do something that I was used to coming from Rails. I really just want to know the 'best' way to have automatic created_at & updated_at column values for records that I insert/update in the database.

Right now I've hooked into the ObjectContext.SavingChanges event and things are working well. After I wrote and tested my code however I realize that there may be a better or faster way to do this.

解决方案

One method is to use insert/update triggers to set the created_at and updated_at columns to the current time.

The Insert trigger would look something like this:

CREATE TRIGGER [dbo].[inserted_myTable] ON [dbo].[myTable] AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[myTable] Set AdmitDateTime = GetDate() where pkid in (SELECT pkid FROM Inserted)
END

The Update trigger would look something like this:

CREATE TRIGGER [dbo].[updated_myTable] ON  [dbo].[myTable] AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[myTable] Set AdmitDateTime = GetDate() where pkid in (SELECT pkid FROM Inserted)
END

One advantage of the trigger approach is that the time/date will always be in the same time zone. Another advantage is that if someone modifies the database record outside of your application, the fields are still updated.

这篇关于Sql Server（实体框架）：created_at，updated_at列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！