question

CSDileepkumar-4184 avatar image
0 Votes"
CSDileepkumar-4184 asked MelissaMa-msft commented

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Viorel-1 commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

we do not have a trigger I have added default value as (getdate()) and we do not need to add a timestamp in the update statement it should automatically update when data is updated

If we want to add trigger to capture date time and user details can you please share any documnetation

Reagards,
Dileep

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 CSDileepkumar-4184 ·

The trigger knows the current time and will be able to store it to database. But how will it know the details about the user who updates the table? Such details probably must be received and saved by your update procedure, not by trigger.


0 Votes 0 ·

Hm, Viorel, I guess that it depends on the nature of the application. For a plain Windows application, original_login() will do.

If there is a middle tier, for instance a web server, which logs in with an application account, we cannot use original_login(). But we can set the user name with sp_set_session_context on connection and then collect that name with session_context() in a trigger.

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @CSDileepkumar-4184,

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.



output.png (5.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Melissa ,
I will try this to create a trigger

Regards,
Dileep

0 Votes 0 ·

Hi @CSDileepkumar-4184,

Could you please provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

If you still face any issue with your trigger's creation, please provide the DDL and some sample data of your tables together with the trigger you created so that we would proceed.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·