June 8, 2017 at 12:04 pm
I created a small table called "Department". Values are (DepartmentID which is autopopulated, Name, GroupName, Created_By varchar (30) CONSTRAINT CreatedByuser DEFAULT(SUSER_NAME()) which should populate currently logged in user. updated_By varchar (30) CONSTRAINT updatedByuser DEFAULT(SUSER_NAME()) Date_Created DATETIME NOT NULL DEFAULT GETDATE(), and Update_Date DATETIME NOT NULL DEFAULT GETDATE(). So this is what I am trying to do and I am not sure if this is possible. What I want is that every time a user updates any records in this table, Updated by and Update_date should be populated with current user and current system time. Is it possible? If so, How? Let me know if I can provide with more details.
June 8, 2017 at 12:09 pm
This can be done using a stored procedure to update or create an update trigger.
June 8, 2017 at 12:23 pm
Joe Torre - Thursday, June 8, 2017 12:09 PMThis can be done using a stored procedure to update or create an update trigger.
Thanks for the reply but I am wondering if you can help me with the query cause when it comes to writing a TSQL, I am like Donald who is there to do a job but suck at it?
June 8, 2017 at 12:31 pm
This works. But what I want to keep track of Modified_Date as well? Does that mean a new trigger?create trigger trg_Update
on Department
after update
as
set nocount on
update Department
set Updated_By = SUSER_SNAME()
where DepartmentID in (select DepartmentID from inserted)
June 8, 2017 at 1:39 pm
newdba2017 - Thursday, June 8, 2017 12:31 PMThis works. But what I want to keep track of Modified_Date as well? Does that mean a new trigger?create trigger trg_Update
on Department
after update
as
set nocount on
update Department
set Updated_By = SUSER_SNAME()
where DepartmentID in (select DepartmentID from inserted)
You can just add it to your existing trigger - alter trigger trg_Update
on Department
after update
as
set nocount on
update Department
set Updated_By = SUSER_SNAME(),
Modified_Date = GetDate()
where DepartmentID in (select DepartmentID from inserted)
Sue
June 8, 2017 at 2:16 pm
Grazie.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply