Is there a way in SQL to ask "what changed?"?

Question

Often I find myself working on a new project with a giant RDBMS whose schema I don't yet understand. I want to be able to mark a checkpoint in the database, do something in the app, and then go back to the database and ask it which tables changed (and ideally, what records changed).

Is there a way to do something like this with SQL? I found this, but it's MSSQL-only (which I'm not using).

It doesn't need to be super efficient. It's just going to be on my dev box and I can wait a few seconds if need be, but I am looking for something better than "dump the whole DB twice and diff the results".

Answer 1

If your DBMS supports query logging, turn it on and tail -f the log.

Answer 2

SQL Server and Oracle both have a feature you can enable called "Change Data Capture". You said you're not using SQL Server, but there's still hope here if you're on Oracle. Perhaps you should mention what dbms you use.

Answer 3

I'm pretty I once came across a MySQL proxy tool that sat between you and the server.

I doubt you'll come across one solution that will work on every SQL DB. (Dump all the table to .SQL or .CSV files before and after and diff them?)

Answer 4

• Add triggers to all tables that log all changes.
• Check if you can hook into the data access layer of your application to log things
• or if it supports logging out of the box. (I think ODBC on Windows supports this.)

Answer 5

Your best bet would be to see if your RDBMS offers a proprietary feature that would do this, or add update triggers that would log events.

Answer 6

In mysql, I use a column

modified timestamp

to retrieve the rows that changed