IMO these types of calcs are better done in your app, where you can at least write a test and assert it's doing the right thing
1) the assertion that code in the DB can’t be tested is a bizarre and unfounded one
2) in any serious organisation there may be dozens of apps in a dozen different languages talking to the DB. Do you seriously propose implementing the same thing in each one, or doing it once in the DB and knowing it’s correct for everyone?
I disagree with the parent comment, but tucking logic away in different parts of your DB does come at a cost. It increases the burden on the engineer who’s trying to understand it. Read the code > look at the schema is no longer enough. Now you also have to know where all of the strongly coupled business logic is inside the DB. Triggers and views can be especially dangerous in a complex system, and having to keep a mental view of how all these discrete resources work together can lead to all kinds of failure.
Triggers tend to generate all kinds of surprises when you change something in a part of your database and suddenly other seemingly unrelated things begin to change. Views can be hard to understand if you have views that use views, use views, and so on. But I don't see any problems with functions and stored procedures as long as you put them in a separate schema from your tables. A function in SQL shouldn't be more tightly coupled than a function in Ruby or Java.
Why is a trigger any more surprising than any callback style interface? Or using inotify (Linux) or reparse points (Windows)? Triggers are very easily discoverable, they are attached along with their source code to the table!
A view is just a named select statement, that’s all it is.
> Why is a trigger any more surprising than any callback style interface?
A procedure call is explicit, a trigger is implicit. You don't call a trigger, it just happens as a side effect of something else. People tend to forget implicit things. Suddenly you notice that something is acting strangely or slowly in your application. You can look at your functions and procedures and try to find the problem. But if your application is full of triggers, how do you know what is going on? A trigger can change a dozen rows, which in turn can change other rows, so changing a single row can trigger thousands or millions of triggers. Also, triggers are not fired in a particular order, the database is free to change the query plan according to what it thinks is best at the moment, so triggers are not deterministic. Triggers can sometimes work and sometimes not.
In summary, triggers are implicit, have side effects and are not deterministic. They are confusing and surprising. Almost everything that can be done with a trigger can be done with a procedure, but explicitly, deterministically and in most cases even without side effects.
I don’t think there’s anything wrong with any of these features, I’ve used all of them myself at various times. The problem though is that whenever you use them, you’re introducing additional complexity to your application, so you have to decide every time whether it’s worth it. If you use these features without proper consideration, you can easily end up with a mess of interdependent schema objects. It’s easy enough to get to a state where it’s difficult to visualize code flows, and in that case making changes will become riskier because all of those features can produce major gotchas.
1) the assertion that code in the DB can’t be tested is a bizarre and unfounded one
2) in any serious organisation there may be dozens of apps in a dozen different languages talking to the DB. Do you seriously propose implementing the same thing in each one, or doing it once in the DB and knowing it’s correct for everyone?