问题关于DateCreated和DateModified列 - SQL Server [英] Question About DateCreated and DateModified Columns - SQL Server

问题描述

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

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

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在记录字段中发生更改/修改时保持更改。

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.

换句话说，你能写一个示例快速触发器吗？我还不知道...

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

推荐答案

您可能需要查看创建更新触发器以更新此值

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

查看类似于

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

这篇关于问题关于DateCreated和DateModified列 - SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！