CODEWITHSUNDEEP
mysql
Jeff
Jeff

Reputation: 4423

Track who changed a field in mysql

Is it possible to tell if a specific field in a database is updated by a specific username in mysql?

example:

DBNumber1 contains contactsTable. contactsTable contains firstName, lastName, phoneNumber.

UserA (db account) creates a record (id=102). Every field for that record was "last updated by" him on a date and time. UserB updates that record (102). He changed an erroneous phoneNumber. firstName and lastName should be "last updated by UserA on date+time" and phoneNumber should be "last updated by UserB on different date+time."

What I have done so far has been add a column to the db that is last updated by, but I would like this to be for every field, not just the record. Is this possible?

Upvotes: 0

Views: 58

Answers (2)

Bohemian
Bohemian

Reputation: 424993

Create a trigger that executes on update. Have it compare old and new values and take action, like inserting transactional data such as username and current time, row id etc in a separate "logging" table.

Upvotes: 1

nl-x
nl-x

Reputation: 11832

I'm almost certain that Mysql is not aware of this. Bit what you can do is add a last_updated column for each column you want to keep record of. Or turn on binary logs (you'd need to check if the logs contain usernames, I guess they do) if you need non-automated logging. However, fetching data from there would be involve quite a lot of work (relatively) so this is only if you think you will need it very little.

Upvotes: 0

Related Questions