0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Itay Moav -Malimovka
  • 52,579
  • 61
  • 190
  • 278
  • 1
    Use a **set based** `AFTER UPDATE` `TRIGGER` – Thom A Nov 02 '22 at 16:10
  • ON UPDATE does not exists in ISO standard SQL language specification. It is a trick of MySQL introduced because there was no trigger in MySQL... And it is a stupid behaviour, because it will runs everytime even it is not necessary... The standard ISO SQL language uses triggers to do so. – SQLpro Nov 03 '22 at 09:27

1 Answers1

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
Tim Jarosz
  • 1,133
  • 1
  • 8
  • 15
  • thanks, I am used to MySQL, where the row identifier is built into the trigger. Actually, If I am using it BEFORE UPDATE am I able to modify what is about to be updated? – Itay Moav -Malimovka Nov 02 '22 at 17:23
  • @ItayMoav-Malimovka: SQL Server doesn't have any `BEFORE UPDATE` triggers .... – marc_s Nov 02 '22 at 17:42
  • See this question about BEFORE UPDATE triggers in SQL Server: https://stackoverflow.com/questions/642822/how-can-i-do-a-before-updated-trigger-with-sql-server – Tim Jarosz Nov 02 '22 at 17:46