CODEWITHSUNDEEP
sqldatabaselogging
rsmith
rsmith

Reputation: 59

What is the best method of logging data changes and user activity in an SQL database?

I'm starting a new application and was wondering what the best method of logging is. Some tables in the database will need to have every change recorded, and the user that made the change. Other tables may just need to have the last modified time recorded.

In previous applications I've used different methods to do this but want to hear what others have done.

I've tried the following:

  1. Add a "modified" date-time field to the table to record the last time it was edited.
  2. Add a secondary table just for recording changes in a primary table. Each row in the secondary table represents a changed field in the primary table. So one record update in the primary could create several records in the secondary table.
  3. Add a table similar to no.2 but it records edits across three or fours tables, reference the table it relates to in an additional field.

what methods do you use and would recommend?

Also what is the best way to record deleted data? I never like the idea that a user can permanently delete a record from the DB, so usually I have a boolean field 'deleted' which is changed to true when its deleted, and then it'll be filtered out of all queries at model level. Any other suggestions on this?

Last one.. What is the best method for recording user activity? At the moment I have a table which records logins/logouts/password changes etc, and depending what the action is, gives it a code either 1,2, 3 etc.

Hope I haven't crammed too much into this question. thanks.

Upvotes: 4

Views: 5319

Answers (2)

Matvei Antipov
Matvei Antipov

Reputation: 66

I know it's a very old question, but I'd wanted to add more detailed answer as this is the first link I got googling about db logging.

There are basically two ways to log data changes:

  • on application server layer
  • on database layer.

If you can, just use logging on server side. It is much more clear and flexible.

If you need to log on database layer you can use triggers, as @StanislavL said. But triggers can slow down your database performance and limit you to store change log in the same database.

Also, you can look at the transaction log monitoring. For example, in PostgreSQL you can use mechanism of logical replication to stream changes in json format from your database to anywhere.

In the separate service you can receive, handle and log changes in any form and in any database (for example just put json you got to Mongo)

Upvotes: 4

StanislavL
StanislavL

Reputation: 57381

You can add triggers to any tracked table to olisten insert/update/delete. In the triggers just check NEW and OLD values and write them in a special table with columns

table_name
entity_id
modification_time
previous_value
new_value
user

It's hard to figure out user who makes changes but possible if you add changed_by column in the table you listen.

Upvotes: 1

Related Questions