Storing User/Date Time on update
-
Hi All,
We need more information about who is updating or inserting new records in the database. I found some information about how to do the Date/Time part:
CREATE TRIGGER trg_updatedt ON [dbo].[tablename]
FOR UPDATE
AS
UPDATE tablename
set modified = getdate()
from inserted
where tablename.colid = inserted.colid
and I found code for the user:
SELECT USER_NAME()
But how do I fit in the Username in this trigger? I'm new to T-SQL.
rgds,
Koen
-
CREATE TRIGGER trg_updatedt ON [dbo].[tablename]
FOR UPDATE
AS
UPDATE tablename
set modified = getdate(), modified_user = suser_sname()
from inserted
where tablename.colid = inserted.colid
check out which user you want to store user_name() <> suser_sname() ... read http://www.sqlservercentral.com/columnists/ckempster/sqlserversecuritypart3.asp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The above advice is the best way. I've written a few articles on auditing SQL Server and this is what I typically do.
-
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply