For the simple count/max aggregates on a single relation that the blog post is talking about, the trigger logic seems straightforward - you just need a trigger on a single table, for insert/delete/truncate, + update for max or if there are conditions. If there's more than one relation involved or if the condition is complex, then it can get pretty complicated.
This is right. Having a single materialized view containing nested queries is not a good idea. But you can factor out the nested queries into their own materialized views. The propagation of updates between materialized views can achieve an effect similar to that of complex triggers.
I should've been more careful with my original comment, though. I'm not saying triggers are worthless. But, whenever a materialized view can do the job, it's usually preferable.
Not affiliated, not even a user, just found it interesting when it was posted here.
The point is you're suffering the same problem. Whether you do it at the application level or the database level, it's a mess.
It doesn't free you from programming, from reasoning about your code, or from ensuring that this code is free of bugs. But it does free you from worrying about whether the bugs exist in multiple places or whether there are subtle race conditions involved in the code interacting with itself.
Whether this important depends on how many applications are accessing the same database. There's a strong argument for one app = one DB, if you can get away with it, and then none of this is relevant. This is not always possible.
The relational algebra that normal forms are expressed in has no concept of aggregate functions.
The example the article gives uses fields like numUnreadRooms or lastSeenTimestamp. These are derivable from the contents of other tables with SQL aggregates like COUNT() or MAX(), but the relational algebra itself would treat them as facts of the relation itself, dependent upon the primary key. And they'd be updated by the application itself.
In other words, having numUnreadRooms or lastSeenTimestamp in your schema does not make it denormalized.
I was under a similar misconception for the first 5-6 years of my career, and then when I realized that it's perfectly fine to store these aggregates inline, my database queries suddenly started performing acceptably.
As for how to update them - in non-broken databases, the solution is usually triggers. A trigger is a stored procedure that executes when a database operation makes some condition true, and then is executed entirely in the DB, with full transactional awareness. So you could listen for inserts and deletes and then update the count on them, and if your insert failed or the transaction was rolled back, your trigger would never be executed. This keeps data integrity in the database, but still gives you efficient queries and an application interface that's consistent.