By checking the date columns:
To only check for new comments, new posts etc., you may create a cron job & then check the comment_date, post_date etc. to determine if anything new appeared in those tables. Save a last checked timestamp from the cron job, so any row with date greater than the last checked timestamp is a new insert.
Using WordPress API filter / action hooks:
WordPress fires some filter / action hooks before/after making database changes.
Usually action hooks are fired after a database action is completed. Often these hooks have name prefix/suffix like added/update/updated/deleted/save/insert etc. Unfortunately, WordPress hooks naming are not consistent, otherwise it would’ve been much easier.
For example, comment API fires the following action hooks after database changes:
wp_insert_comment: fires after a comment is inserted into the
database.deleted_comment: fires after a comment is deleted from the
database.trashed_comment: fires after a comment is moved to trash.untrashed_comment: fires after a comment is moved out of trash.spammed_comment: fires after a comment is marked as spam.unspammed_comment: fires after a comment (previously marked as spam) is unmarked as spam.edit_comment: fires after a comment is updated/edited.comment_closed: fires after a post is marked as not allowing comments.
Sample CODE with an action hook:
add_action( 'wp_insert_comment', 'wpse_comment_inserted' );
function wpse_comment_inserted( $comment_id, $comment_object, 99, 2 ) {
// comment with id $comment_id is inserted into the database,
// do something with it here
}
There are many other filter hooks and action hooks that run before / after database changes. Use them as you need.
Advantage of using these hooks is that, since they are part of WordPress core, you’ll not have to worry about internal changes. The disadvantage is however, that there may be plugins / themes that don’t use these core API(s), instead runs their custom constructed queries. So you’ll not be able to catch DB changes from those.
By using query filter hook:
It’s also possible to track Insert/Update/Delete queries made through WordPress $wpdb API. Any database query that uses WordPress Database API, uses $wpdb (an instance of wpdb class). All the queries (including insert, update and delete) fire query filter hook. So using this hook you can filter all the queries just before they are executed.
However, it’ll not work if any theme / plugin makes custom DB queries outside of WordPress $wpdb API or if any core query runs before the plugin is loaded or if any external application makes the changes through custom SQL queries.
By extending wpdb class:
Also, it’s possible to create a db.php file, in the wp-content directory (or create a soft link in wp-content from a plugin) and extend the wpdb class from there. This way you may enhance any method of wpdb class and track the insert/update/delete queries from there.
By using database trigger:
However, to track any database change by any SQL query, the only reliable and complete way is to create triggers upon plugin activation and then track the database changes through the trigger. Although, to do this from a plugin, the Database credential you are using for WordPress must have create / drop trigger privilege.
Caution: Too many triggers may slow down DB queries. Also, make sure to clean up the triggers at the time of plugin deactivation.