Database events but not triggers

Question

This is a question regarding general DB inner workings, not particular to an implementation or paradigm, though answers for certain technologies are welcome. I am asking if there is a way to listen to what commands the database has received, or something like read an inner log of the database, at least the last changes. I need such a functionality to be able to find if there were changes to a table and if so, read the particular row that has changed. It is assumed changes to the columns are not to happen.
I am only a listener of the database, hence I am not able to program triggers.

Answer 1

Most major DBMS products today have facilities for this: it may be called

• Change Data Capture (Oracle, SQL Server)
• logical replication (Postgres, MySQL)

Most of these facilities operate on the publish/subscribe model and build upon the DBMS's built-in transaction logs to provide a stream of change events. Often the amount of logging will increase slightly because the primary key has to be included in the stream rather than just physical information about which blocks have changed.

Organisationally, you may encounter resistance to implementing things like this with conservative DBAs and organisations (I have encountered this several times when trying to implement Oracle CDC), while it's not likely to be an issue where they have switched to, or are using open-source DBMSes (more open to change).

Answer 2

For SQL Server, you have three options for looking at what has happened within the system. First are the Dynamic Management Views (DMV). However, they're either immediate, this query is running, or aggregations. Further, it's only the stuff that's currently in cache. Another choice is Query Store. It's also an aggregate, but one broken up, by default, into one hour chunks. It gives you a much clearer picture as to when a particular query ran. The last one is Extended Events. This gives you detailed information about the queries at a very granular level. It's weakness is that it's going to provide you with LOTS of data unless you carefully filter it on capture.

Oracle has similar functionality. I don't know ElasticSearch well, but I'm sure it does too.

The only one that's guaranteed to get you to the specific row changed is something like Extended Events.

As was stated earlier, there are other auditing tools that you can add to your databases, but they do change the data structure and data management, so you'll want to take that into account when comparing whether you want to capture all the changes or you want to monitor the queries and figure out the changes based on the queries.

Answer 3

Also @StefanPopescu take note that pretty much any auditing feature (at least in Microsoft SQL Server, but likely true in most modern RDBMS) will require more than just read-only access to the database to at least enable the feature and/or configure it.

In Microsoft SQL Server, database level Triggers work really well to audit schema changes, and can be used to log data changes as well.

In addition to the other recommendations there's also:

1. Temporal Tables - Which functions similar to Change Data Capture and log data changes to a table.
2. Auditing - Which logs events at the server and database level, so can be configured to log the changes you mentioned.

Answer 4

Note that the following "auditing" mechanisms do not tell you who did the operation:

• Temporal Tables
• Change Data Capture
• Change Tracking

Server Audit does, however, but it has weaknesses when it comes to parameterized SQL (you won't see the value in the WHERE clause, for instance). Server Audit is built on top of Extended Events.

Also, note that I added one suggestion not mentioned in other posts, above: Change Tracking.