How to make created_at and updated_at fields in MsSQL?-CodePudding

I want to make created_at and updated_at in MsSQL.

Here is query:

CREATE TABLE current_timestamp_demos
(
    id         INT PRIMARY KEY IDENTITY (1, 1),
    msg        VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);

With created_at everything is fine, but on filed updated_at - here, ON UPDATE CURRENT_TIMESTAMP I get an error. DataGrip say something about dialect, and this is MySQL dialect, I guess.

So, how can I create those created_at and updated_at fields? I've created created_at, but with updated_at I have some problems.

CodePudding user response：

The complete code :

CREATE TABLE current_timestamp_demos
(
    id         INT PRIMARY KEY IDENTITY (1, 1),
    msg        VARCHAR(255) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    updated_at DATETIME2 
);
GO

CREATE TRIGGER E_U_CTD
ON current_timestamp_demos
FOR UPDATE
AS
UPDATE T
SET updated_at = SYSDATETIME()
FROM   current_timestamp_demos AS T
       JOIN inserted AS i
          ON T.id = i.id;
GO

Also use SYSDATETIME() instaed of CURRENT_TIMESTAMP.