A trigger on INSERT or UPDATE of a watched entity should kick off an INSERT into a message queue, and a trigger on INSERT in the message queue should run a script to send the message where it needs to go. Now you have a self-managing, self-logging, asynchronous system that only uses system resources when needed.
Yes, exactly. OK, sometimes it can actually be a little tricky to figure out who needs notification and what data they need, but that's application/business logic that just needs to be worked out. (Example: client was not interested in a particular "master" record, and therefore has not received it, but the addition of a particular "detail" record means that client is now interested, so sending the changed record is not enough, the update needs to include the master and all related details. And so on and so forth, for a really rich app with rich workflow, there winds up being a surprising amount of logic required to get the right updates but no more than needed out to each client.)
This was solved decades ago. Next question, please.
Well... No, not really, at least not that I know of. The ability for "trigger on INSERT in the message queue should run a script to send the message where it needs to go" has actually *not* been around for decades. For way too long, this had to be a process *polling* the message queue. Triggers were limited to whatever "procedural SQL" flavor your db had, and had no access to file or network resources. Now you can write triggers in many languages, sometimes with full access to the system. Now you can from a SQL trigger send a notice, which a client can listen for via select/kqueue/libevent etc. But that's all much more recent.