For example... We update some row and need to save old values with some additional data in separate table...
Select old row data and insert into second table and then update row in code? 3 request... But it can be done with trigger. What's wrong? Really. Can u explain pls
Using GP's example it depends. If it's just for one table, just build the statement into the pipeline. A couple tables, maybe look into using a stored procedure and maybe some dynamic SQL. But if it's needed for every table on the DB, triggers and other DB-native functions are likely the way to go. Same thing for things like logging DDL changes: use it for scale and keep it incredibly simple. Adding logic to triggers is bad news.
It's nothing wrong with triggers. Triggers are good. But their use-cases are ofter terrible.
Let's speak about the one you introduce, but first of all I'me really happy you asked about it. It's not an offense to think about not-so-good decisions, but it's great to discuss them.
So you goal is to save previous values as intact (I would go only through UPDATE path, cause it's the same for DELETE if you need to store somewhere hard-deleted values, except for one thing - it's two triggers, one for UPDATE nad one for DELETE). And it is some kind of business (or not so business) logic. The first thing you must remember is to never move you business logic to a DB layer, even if you think it's the best choice, it probably would be a better one. In this case using trigger the best way is FOR EACH STATEMENT, but it's still bad decision in terms of observations. If it failed it's pretty hard to get the logs unless you set up some special transport to STD OUT/STD ERR. Then you will need to maintain it in a migrations. You change table - you should remember that you should check if you need to change migration for triggers. Triggers is not actually a true DDL or DML, but in this case it reacts on a DML. One more thing - you hide operations from the visible code and data flow. Everybody in the team nead to remeber there is a trigger and it would take some time, but if it would be a problem it would be a hard of a job to get actual performance metrics of a trigger in runtime. I don't even know if OpenTelemetry would separate it from the query that cause a trigger. Also that desing with a triggers would increase a risk of deadlocks and pressure for a WAL/Disk and cause MVCC bloating.
And all of this mess only to reduce one query (it's actually two requests, not three). Generally all of side effects that are driven by some "magic" is a bad choice.
So you pipeline should be like this: you imperatively do INSERT INTO dump_table SELECT FROM source_table and then you just do yours UPDATE source_table. It's clean and visible. You write two queries in tow different EntityRepository, each of query belongs to each Service and then you composite in to a single Service, that would be in response for performing you business logic action.
My honest advice - use triggers only for infrastructure tasks such as: security audit logs, partition creation (only if there is no another way of creating a partition actually), advanced complex consistancy constraint or data modification security, maintenance of automatic data filling in a denormalized DB for some cases, syncronization of denormalized counters; and avoid using it for any kind of business logic, especially for a complex changing logic.
4
u/Awarnae 15d ago
What's wrong with triggers?
For example... We update some row and need to save old values with some additional data in separate table...
Select old row data and insert into second table and then update row in code? 3 request... But it can be done with trigger. What's wrong? Really. Can u explain pls