How to add updateTime col to sql server 2017

Question

I have DB and i insert createdTime col with getDate and it work fine. now i want to add col "Update time", how i do it? Thanks!!!

You need a trigger for `updatetime` in SQL Server (or you need to set the value explicitly in the `update` statement). — Gordon Linoff, Sep 16 '19 at 12:42

score 1 · Answer 1 · answered Sep 16 '19 at 13:15

As @Gordon Linof stated by commenting your question, you can achieve this by using a trigger.

Here you can find practical example:

CREATE TRIGGER [dbo].[xxx_update] ON [dbo].[MYTABLE]
FOR UPDATE
AS
BEGIN

    UPDATE MYTABLE
    SET mytable.CHANGED_ON = GETDATE()
        ,CHANGED_BY = USER_NAME(USER_ID())
    FROM inserted
    WHERE MYTABLE.ID = inserted.ID

END

Source: SQL Server after update trigger

Anyhow, i would prefer to let the application update the CHANGED_ON column while running the update statement.

You left out the join between `mytable` and `inserted`. – shawnt00 Sep 16 '19 at 21:01 — shawnt00, Sep 16 '19 at 21:01

score 0 · Answer 2 · answered Sep 16 '19 at 20:40

0

I found easy way to do this:

1.Add col dataModified to you table

2.Write this code

CREATE TRIGGER trg_update_my_table on my_table FOR UPDATE AS            
BEGIN
    UPDATE my_table
    SET dataModified=getdate()
    FROM my_table INNER JOIN deleted d
    on my_table.id = d.id
END

answered Sep 16 '19 at 20:40

Baruch Mashasha

951
1
11
29

That's the same answer you got 7 hours ago from B3S. – shawnt00 Sep 16 '19 at 20:54
No its not the same, i try it and it not work. my Solution is very clear and work. – Baruch Mashasha Sep 16 '19 at 20:58

How to add updateTime col to sql server 2017

2 Answers2