关于DateCreated和DateModified列的问题 - SQL Server

CREATE TABLE Customer
(
        customerID         int identity (500,20) CONSTRAINT 
        .
        .
        dateCreated    datetime DEFAULT GetDate() NOT NULL,
        dateModified   datetime DEFAULT GetDate() NOT NULL
);

When i insert a record, dateCreated and dateModified gets set to default date/time. When i update/modify the record, dateModified and dateCreated remains as is? What should i do?

当我插入记录时，dateCreated和dateModified设置为默认日期/时间。当我更新/修改记录时，dateModified和dateCreated保持不变？我该怎么办？

Obviously, i need to dateCreated value to remain as was inserted the first time and dateModified keeps changing when a change/modification occurs in the record fields.

显然，我需要将dateCreated值保持为第一次插入，并且dateModified在记录字段中发生更改/修改时不断更改。

In other words, can you please write a sample quick trigger? I don't know much yet...

换句话说，你能写一个快速触发样本吗？我还不太了解......

7 个解决方案

#1

You might want to look at creting an update trigger to update this value for you

您可能希望查看创建更新触发器以更新此值

Have a look at something like

看看像

CREATE TABLE Vals(
        ID INT,
        Val VARCHAR(10),
        DateCreated DATETIME DEFAULT GetDate(),
        DateUpdated DATETIME DEFAULT GetDate()
)
GO

CREATE TRIGGER Upd ON Vals
AFTER UPDATE
AS 
UPDATE Vals
SET     DateUpdated = GetDate()
FROM    Vals INNER JOIN
        inserted ON Vals.ID = inserted.ID
Go

INSERT INTO Vals (ID, Val) SELECT 1, 'A'
SELECT *
FROM    Vals
GO

UPDATE Vals SET Val = 'B'
SELECT *
FROM    Vals
GO

DROP TABLE Vals
GO

#2

UPDATE
    Customer
SET
    ... = NewValue,
    dateModified = DEFAULT
WHERE
    ...

I'd use this rather than dateModified = GETDATE() so GETDATE() is only used once (say you want to change to GETUTCDATE() in future)

我使用它而不是dateModified = GETDATE（）所以GETDATE（）只使用一次（假设您希望将来更改为GETUTCDATE（））

Or a trigger if you have multiple update paths...?

或者，如果您有多个更新路径，则触发...？

#3

When i insert a record, dateCreated and dateModified gets set to default date/time. When i update/modify the record, dateModified and dateCreated remains as is? What should i do?

当我插入记录时，dateCreated和dateModified设置为默认日期/时间。当我更新/修改记录时，dateModified和dateCreated保持不变？我该怎么办？

A Column default is only used when INSERTing and not by an UPDATE. The default will be used by the INSERT command if you do not supply the column or issue the DEFAULT keyword in the INSERT.

Column默认值仅在INSERTing时使用，而不是在UPDATE时使用。如果您不提供列或在INSERT中发出DEFAULT关键字，则INSERT命令将使用默认值。

INSERT INTO Customer (col1, col2) 
VALUES (..,..)  ---get default for dateCreated & dateModified   

INSERT INTO Customer (col1, col2,dateCreated) 
VALUES (..,..,DEFAULT)  ---get default for dateCreated & dateModified   

INSERT INTO Customer (col1, col2,dateCreated,dateModified) 
VALUES (..,..,DEFAULT,DEFAULT)  ---get default for dateCreated & dateModified   

INSERT INTO Customer (col1, col2,dateCreated,dateModified) 
VALUES (..,..,'1/1/2010',DEFAULT)  ---only get default for dateModified   

INSERT INTO Customer (col1, col2,dateCreated,) 
VALUES (..,..,'1/1/2010')  ---only get default for dateModified   

INSERT INTO Customer (col1, col2,dateCreated,dateModified)
VALUES (..,..,'1/1/2010','1/2/2010')  ---no defaults for dateCreated & dateModifie

I like using a local variable set that the top of the procedure:

我喜欢使用过程顶部的局部变量集：

DECLARE @RunDate datetime
SET @RunDate=GETDATE()

I then use that within the procedure, so all changes (even on multiple tables) have the exact same date to the millisecond. I also prefer the dateModified column to allow nulls and not have a default, when it is inserted, it has been created not modified, I'll set the dateModified when it is actually modified.

然后我在过程中使用它，因此所有更改（即使在多个表上）都具有与毫秒完全相同的日期。我也更喜欢使用dateModified列来允许空值而不是默认值，当它被插入时，它已被创建而未被修改，我将在实际修改时设置dateModified。

then use:

然后使用：

UPDATE Customer
    SET importantColumn=
       ,dateModified = @RunDate
    WHERE ...

UPDATE CustomerPrice
    SET importantColumn=
       ,dateModified = @RunDate
    WHERE ...

#4

@astander is right, you should just use an update trigger if you want this automated. My update triggers are slightly different (I use the 'inserted' virtual table). Here's one that should fit your schema (rename however you see fit):

@astander是对的，如果你想自动更新，你应该使用更新触发器。我的更新触发器略有不同（我使用'inserted'虚拟表）。这是一个适合您的架构（重命名，但你认为合适）：

CREATE TRIGGER [CustomerDateModifiedTrigger] ON [dbo].[Customer] 
FOR UPDATE
AS
UPDATE Customer
SET dateModified = GETDATE()
FROM Customer c
INNER JOIN inserted i ON c.customerID = i.customerID

#5

@Kronass, you don't have any idea about what uou are saying!

@Kronass，你不知道你在说什么！

timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible. For more information, see Data Type Synonyms (Transact-SQL).

timestamp是rowversion数据类型的同义词，并且受数据类型同义词的行为影响。在DDL语句中，尽可能使用rowversion而不是timestamp。有关更多信息，请参阅数据类型同义词（Transact-SQL）。

The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

Transact-SQL时间戳数据类型与ISO标准中定义的时间戳数据类型不同。

The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

不推荐使用时间戳语法。此功能将在Microsoft SQL Server的未来版本中删除。避免在新的开发工作中使用此功能，并计划修改当前使用此功能的应用程序。

rowversion (Transact-SQL) Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

rowversion（Transact-SQL）是一种在数据库中公开自动生成的唯一二进制数的数据类型。 rowversion通常用作对表行进行版本标记的机制。存储大小为8个字节。 rowversion数据类型只是一个递增的数字，不保留日期或时间。要记录日期或时间，请使用datetime2数据类型。

#6

1) Be sure to create the index for the primary key. (I just uncovered a mistake of this type recently.)

1）确保为主键创建索引。（我刚刚发现了这种类型的错误。）

2) You can use one INSERT/UPDATE trigger instead of separate triggers at the price of a tiny loss of efficiency. If insert.DateCreated is null, then update Vals.DateCreated, otherwise update Vals.DateModified.

2）您可以使用一个INSERT / UPDATE触发器而不是单独的触发器，但代价是效率很小。如果insert.DateCreated为null，则更新Vals.DateCreated，否则更新Vals.DateModified。

#7

-2

There is a datatype in SQL Server Called timestamp. which keep track of the row version for each time you modify the row. Or if you want you can use a trigger and change the ModifiedDate Column.

SQL Server Called时间戳中有一种数据类型。每次修改行时都会跟踪行版本。或者，如果您需要，可以使用触发器并更改ModifiedDate列。

#1