June 30, 2011 at 4:55 pm
Hi folks,
We have a front end(Access) which uses sql server as a back end. We give 2-3 tables where user can view as well as to update or insert the data in a table.
I have columns username,updatetimestamp on all this tables. when user insert data it will insert their name and update timestamp by default.
Now my question is if user update any records in TABLE i want to update the username ,updatetimestamp..
how can i do that???
Correct me if i'm wrong.
I am thnking to write a update trigger . Will i get what i want from update trigger???
i have 8-9 columns in that table. User can edit any record. In this case how can i update my table with current username and updatetimestamp.
Thanks,
SAM
July 1, 2011 at 11:34 am
Are you using the SQL Server backend to handle updating of the records, or is that being done through Access?
IE - are you running a stored procedure on SQL server when a user updates a record, passing it the record ID and the updated values?
If so, the best thing to do is to just append to the UPDATE statement in your stored proc, the name of the user as well as GETDATE(), which is the current date and time.
If you want to use an UPDATE trigger, you won't be able to pass it any parameters. So, the closest thing you'll be able to get to a user name is the name of the user that the connection to the database is using. You can get that by using SUSER_SNAME, by running this query:
SELECT SUSER_SNAME()
You can set the UPDATE trigger up like:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [NAME]
ON
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE t
SET t.UserName = (SELECT SUSER_SNAME()),
t.UpdateDate = GETDATE()
FROM
t
JOIN [INSERTED] u ON t.ID = u.ID
END
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply