Is there a way in SQL to ask “what changed?”?

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".

Answers


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


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.


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?)


  • 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.)

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.


In mysql, I use a column

modified timestamp

to retrieve the rows that changed


Need Your Help

cocos2d+box2d application freeze/stopped

ios iphone cocos2d-iphone box2d

My cocos2d+box2d application freeze/stop sometimes. Pause from debugger get this line from b2World.cpp. What does it mean? Maybe problem with my contactlistener, without it app work at normal mode....

dynamic jquery ui selectmenu not styling

jquery jquery-ui jquery-ui-selectmenu

I am using multiple select menu's using http://wiki.jqueryui.com/w/page/12138056/Selectmenu . They work great when initialized with the page but dynamic select menu's do not style and look like no...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.