SXI Forum

SeanR

Administrator

Registered: 20-11-2018

Posts: 148

Check if a database table has been modified

Sometimes I need to check a relitively static table (a table that doesn't change too often).  It is not always possible to add a trigger to a table, however this would be an excellent way for the DB to announce that a change was persisted. An easy way to check in MYSQL/MariaDB though, is using the following query:

MYSQL

select table_schema as database_name,
       table_name,
       update_time
from information_schema.tables tab
where update_time > (current_timestamp() - interval 30 day)
      and table_type = 'BASE TABLE'
      and table_schema not in ('information_schema', 'sys',
                               'performance_schema','mysql')
      -- and table_schema = 'sigreg' 
order by update_time desc;

The result will look something like the following:

database_name    | table_name    | update_time
---------------    |-----------    |-------------
xlayerrestapi        | sigreg           | 2023-02-21 14:08:35.000

You can now save the update_time and if, on a later query, the update time is newer than the last update time you know that the table changed somewhere.

NOTE:  The update_time will change if a record is added / deleted or updated.

An explaination of the query can be found here

This is useful for MYSQL and MariaDB. 

1. What other ways have you found to monitor a table in a database for changes?

2. What suggestions are there is we only want to know about new records being added?