Difficult Update

Question

Difficult Update

James Asante

Old Hand

Points: 361
More actions
March 30, 2005 at 7:38 am

#61542

List 1
SQL Server:
HQ-STT-2
Database:
EUTSchedule
Table:
Users
Key Field
UserID
Other Fields:
UserName, Password, LastLoggedIn

List 2
SQL Server:
HQ-STT-2
Database:
FromTrainerSQL
Table:
FromTrainersTable
Key Field:
FTKey
Other Fields:
User_name1, Day1, Timestamp1

The Trainer logs in using his details in List 1 and the LastLoggedIn field is time stamped with the current date and time.

Here is my request:
I want the current UserName in List 1 to be stamped in User_name1 in List 2 when Day1 field is updated. I already have the trigger to update Timestamp1 – thanks to sqlservercentral.com.
Any help will be appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

vadba SSChampion Points: 11133 More actions · Answer 1

What do you mean by "I want the current UserName in List 1 to be stamped in User_name1 in List 2" ? Please clarify your request.

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

I am asking if it is possible to update User_name1 with the current user who has logged in the wesite with details in List 1.

Thanks.

vadba SSChampion Points: 11133 More actions · Answer 3

You didn't provide the information to determine how the two tables are joined, so I couldn't fill that part in (which I highlighted in the UPDATE statement). Also, I assumed that the tables are owned by dbo (database owner).

In your UPDATE trigger for FromTrainerSQL.FromTrainersTable, you could include something like this:

UPDATE FromTrainersTable

SET user_name1 = U.username

FROM FromTrainersTable FT

JOIN EUTSchedule.dbo.Users U ON FT.??? = U.??? -- how are these related??

JOIN inserted I ON FT.FTKey = inserted.FTKey

JOIN deleted D ON FT.FTKey = deleted.FTKey

WHERE D.Day1 <> I.Day1

P.S.

In your posts, it's better to refer to the tables by their names, rather than generic references like "List1" and "List2".

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

Unfortunately, the two tables have nothing in common except that they are both located on the same server.

Thanks