Data in two tables

Question

Data in two tables

James Asante

Old Hand

Points: 361
More actions
May 27, 2005 at 10:56 pm

#86880

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

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 !!!**

James Asante Old Hand Points: 361 More actions · Answer 2

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

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 !!!**

James Asante Old Hand Points: 361 More actions · Answer 4

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.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

James - use "select suser_sname()"

**ASCII stupid question, get a stupid ANSI !!!**

James Asante Old Hand Points: 361 More actions · Answer 6

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