CODEWITHSUNDEEP
mysqlinsertsql-update
job kwemoi
job kwemoi

Reputation: 65

How to know the user who modified the table in mysql

I have a question on mysql tables and users. I need to know which user modified a table. I have 2 tables. 

   1. user (id,username, password and dtime)
   2. author (surname,firstname,email,phone, LastModified, userID)

LastModified is the field which writes the timestamp when an update is made. I want to be able to write the user id of the current user so I can know the user who made the update. User id is a foreign key in the table 'author'.

Anyone has ideas how I can do this?

Upvotes: 0

Views: 2754

Answers (1)

Marc B
Marc B

Reputation: 360872

This is not something MySQL can do, unless your users also have mysql accounts that they're using. You will have to modify your client-side code to include the ID of the user on whose behalf the code is running the query, e.g

UPDATE author Set surname='Foo', LastModified=now(), userID=$userID
                                                   ^^^^^^^^^^^^^^^^

MySQL has absolutely NO awareness of what's going in your client-side code, so it's up to you to provide these sorts of details.

If you do have per-user MySQL accounts, then it'd be as simple as

UPDATE author Set surname='Foo', LastModified=now(), userID=USER()
                                                   ^^^^^^^^^^^^^^^

but providing users with direct database logins is almost ALWAYS an incredibly BAD idea.

Upvotes: 1

Related Questions