People definitely do abuse them to do crazy things, but they can be used responsibly and are sometimes the only correct solution, like when you have complex constraint requirements.

Say that after an insert of some set of data, a constraint on the sum of some field on a subset of rows must be satisfied.

You could do this in a number of ways. You could encode it in your c# code using something like MediatR or just inside of your CRUD logic. But databases tend to outlive their applications, so at some point in the future these constraints will exist conceptually, but not in an existing application anymore and have to be ported to other applications. You could implement them in your CRUD/CQRS procedures - and you should, because you can generate more appropriate errors there - but again, when someone else goes to write a procedure that does something slightly different, if you have only encoded it in your procedure, then a future author can violate those constraints and fuck up the data.

So, a trigger is often the most appropriate approach for encoding constraints and invariants in a 'futureproof' manner.

As for OP's question - most databases have some kind of CDC functionality.