0

I am building a spring mvc web app with a MySQL database. I need to be able to record every time a specific user creates, views, or modifies any record in the database. Then, later, I need to be able to retrieve historical access logs broken down by user, showing all of the rows that were accessed by each user, including the dates and times of each create, view, or modify operation by each user on each row.

Is there a tool that I can use to accomplish this? Or do I have to create all this by myself?

I am imagining writing a complicated "shadow application" with three data tables and then model and controller level code throughout my application. Someone must have done this before. Is this built into MySQL already? So I just setup my app to log each user into the database uniquely, and then MySQL records their activities if I configure MySQL to do so? How do I configure MySQL to do this?

CodeMed
  • 9,527
  • 70
  • 212
  • 364

1 Answers1

1

What you are trying to do can be implemented at the database level with triggers. Lots of people are not fans of triggers, so be sure to take this up with the rest of the team.

Examples of how this can be done can be found here and here

Community
  • 1
  • 1
geoand
  • 60,071
  • 24
  • 172
  • 190
  • +1 and Thank you for offering an option. Can you please also tell me why people do not like triggers? – CodeMed Apr 02 '14 at 21:24
  • A few main reasons why triggers are not popular is they are sort of 'magic' (meaning thet you have little or no control over them) and that they move some of the application logic into the database (which in most cases is a bad thing). The following SO question has lots of good information: http://stackoverflow.com/questions/460316/are-database-triggers-evil – geoand Apr 03 '14 at 06:08
  • @CodeMed I am not an expert on stored procedures, but as far as I know they cannot do what you are asking. Think of them as a sort of function that gets invoked explicitly by code, whereas triggers are run by the database in response to some event – geoand Apr 03 '14 at 17:15
  • @CodeMed I looked at the question but I am afraid I don't have any answer for you – geoand May 21 '14 at 06:00
  • Thank you very much for looking into it and for getting back to me. I seem to have found an answer. – CodeMed May 21 '14 at 18:22