How do I set an ON UPATE rule in SQL Server?

Question

In Mysql, I have the ON UPDATE rule which is helpful when I am trying to auto-catch the time a row was updated.

For example:

CREATE TABLE important_action 
(
    myAction VARCHAR(50),
    actionStatus VARCHAR(5),
    `date_modified` timestamp DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP 
        COMMENT 'Data and time record has been modified.'
)

How can I achieve same behavior in SQL Server?

Answer 1

As @Larnu mentioned, use an AFTER UPDATE trigger. However, the issue you have is that without a unique identifier on the table, your update may not be as targeted as you want. You can add some kind of unique identifier field.

CREATE TABLE important_action (
    [uid] int IDENTITY(1,1),
    myAction VARCHAR(50),
    actionStatus VARCHAR(50),
    [date_modified] datetime DEFAULT CURRENT_TIMESTAMP 
);
GO

CREATE TRIGGER important_action_update_date_modified ON important_action
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE important_action 
    SET date_modified = GETDATE()
    FROM important_action as ia
        INNER JOIN inserted i 
            ON i.[uid] = ia.[uid]
    ;
END
GO

INSERT INTO important_action (myAction, actionStatus)
VALUES ('testAction1', 'ts1')
    , ('testAction2', 'ts2')

SELECT * FROM important_action;

UPDATE important_action
SET actionStatus = 'us2'
WHERE myAction = 'testAction2';

SELECT * FROM important_action;

Results: First Select:

uid	myAction	actionStatus	date_modified
2	testAction1	ts1	2022-11-02 12:35:45.740
3	testAction2	ts2	2022-11-02 12:35:45.740

Second Select:

uid	myAction	actionStatus	date_modified
2	testAction1	ts1	2022-11-02 12:35:45.740
3	testAction2	us2	2022-11-02 12:35:45.757