When we update the data in custom table current datetime and current user details can we capture in 2 different columns for custom table?

CS, Dileepkumar 1 Reputation point
2021-08-03T12:39:19.91+00:00

Hello Team,

When I inserted data into the DB table I have created 2 columns to capture the current date time and similarly capturing current user details when I inserted data into the DB.
if data is already inserted in the DB table once we try to update data in the table those 2 columns will not capture the current date time and current user details .

Regards,
Dileep

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,884 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 41,101 Reputation points
    2021-08-03T13:26:55.587+00:00

    That's to less on informations.
    How does your implementation looks like? Column default value, setting the timestamp in UPDATE statement or a trigger?


  2. Erland Sommarskog 102.3K Reputation points
    2021-08-03T18:55:59.42+00:00

    The best way to do this, is write the update as:

    UPDATE tbl
    SET       coL = @somevalue,
           current_time = DEFAULT,
           current_user = DEFAULT
    FROM  tbl
    WHERE ...
    

    Yeah, I know that this is not what you are looking for. You want it to happen automatically, and this can be done with a trigger, but that comes with a performance overhead.

    It is a long-standing request that Microsoft provides something better, and your request is absolutely reasonable. You can vote for a improvement suggestion here: http://feedback.azure.com/forums/908035-sql-server/suggestions/32901964-provide-a-mechanism-for-columns-to-automatically-u

    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2021-08-04T02:40:57.28+00:00

    Hi @CS, Dileepkumar ,

    Welcome to Microsoft Q&A!

    You could refer below example which created one trigger to update the update time and update user automatically.

    Step1: create one table as below.

    create table testtable  
    (id int ,  
    col1 varchar(10),  
    updatetime datetime,  
    updateduser varchar(10))  
      
    insert into testtable values  
    (1,'asd',GETDATE(),'user1'),  
    (2,'zxc',GETDATE(),'user2')  
    

    Step2: create one trigger as below.

    create trigger updatetrigger on testtable  
    after update  
    as  
    update a  
    set a.updatetime=GETDATE()  
    ,a.updateduser=SUSER_NAME(1)  
    from  testtable a  
    inner join inserted b on a.id=b.id  
    

    Step3: update one row and compare the results.

    select * from testtable  
      
    update testtable set col1='b' where id=1  
      
    select * from testtable  
    

    Output:
    120299-output.png

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.