May 27, 2005 at 10:56 pm
I would be very grateful for any ideas to solve this problem.
A login control updates a LastLoggedIn datetime field in a table called USERS. This table contains the User name and other details. The Primary key field is UserID.
The user upadates the Day1 checkbox field of the ATTENDANCE table whilst he is logged in. The two tables have nothing in common.
Is it possible in any way to be able to stamp the name of the logged in user in the ATTENDANCE table in oder to identify who did the update?
What adjustments can I make to the ATTENDANCE table to make it possible.
Awaiting for your suggestions.
May 28, 2005 at 9:32 am
You could add your UserID column as a foriegn key in your Attendance table!
What other fields do you have in your Attendance table ?! Is it possible at all to merge the two and maintain it as one table ?! (remember - sometimes denormalization helps...depends on what fields you have in each table) - hth!
**ASCII stupid question, get a stupid ANSI !!!**
May 28, 2005 at 10:36 pm
Hi,
Yes, I can add UserID to the ATTENDANCE table but how will it be populated?
The attendance table key field is FTKEY, other fields are course and delegates information like CourseCode, CourseDescription etc. In fact the two tables sit in two different databases. A merger is may not be possible because the users table is fixed as it for Trainers.
Thanks
May 29, 2005 at 11:21 am
James,
Can you not update the ATTENDANCE table at the same time as the USERS table is updated ?!
Alternately, I have many "audit tables" in my database where I just use (user_name()) as default in a column for users and (getdate()) as default for time that user made update etc..so this could be another option for you!
hth!
**ASCII stupid question, get a stupid ANSI !!!**
May 29, 2005 at 10:35 pm
Hi Sushila,
Thanks for your suggestion. Is there a way to get the system to read user_name() from the USERS table? It is picking the DBO, and not the logged in user.
Thanks once more.
May 30, 2005 at 6:08 am
James - use "select suser_sname()"
**ASCII stupid question, get a stupid ANSI !!!**
May 30, 2005 at 7:39 am
Hi Sushila,
suser_sname() prints the same user as user_name().
Here is my trigger:
CREATE TRIGGER [track_time] ON dbo.ATTENDEES
FOR UPDATE
AS
UPDATE ATTENDEES
SET timestamp1=GETDATE(), User_name1 =suser_sname()
FROM ATTENDEES
JOIN deleted ON ATTENDEES.FTKey=deleted.FTkey
AND ATTENDEES.Day1<>deleted.Day1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply