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

Question

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

Answer 1

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

Answer 2

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

Answer 3

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:

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.