I’m creating a dashboard which should monitor a database table. I have only database access (no application layer). The table is rather large (10 million rows), however not changing rapidly (100 inserts/updates per minute)
How can I find out whether the table changed? I would try to hit the database every second, but this seems like a brute-force approach…
Databases: MySQL/Postgres
6
You can use triggers.
CREATE TRIGGER notifyMe
ON table1
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB AutoMailer',
@recipients = '[email protected]',
@body = 'The DB has changed',
@subject = 'DB Change';
GO
6
For PostgreSQL I know a way to get notification from the database when a row changes.
- use trigger when insert / update / delete occurs.
- when event occurs send a notify to a client socket.
- be sure you app have a client to the server.
- then your app will receive a notification.
You can see my code or PostgreSQL’s documentation.
It seems the notification is not a reliable notification, but at least it works for me.
1