Why is using a trigger in mysql/oracle/mssql ... in web programming (and not only) considered a sign of shit code?

Question

SQL

Good afternoon.
Get used to using the DBMS capabilities to the maximum. Slowly learning Web programming (node.js way). Often I use both stored procedures and triggers. I believe that if the data in the database is modified, then it is correct if the database itself, in addition to its "traditional" values ​​​​(primary key, indexes, foreign keys, constraints) should monitor the correctness (and not just the integrity) of the stored data, including using triggers ( if, when changing some data, it is required to make changes to others and they are connected by additional calculations that cannot be implemented simply through foreign keys). Also, a number of methods can be introduced into stored procedures (and functions), for example, calculations with complex data preselections.
If the DBMS does this quickly and the calculations are related to the data stored in it, then why do many (including on this site) consider this to be shit code? Is it a good style to drive data from tables between the backend and the DBMS for calculation?
One answer I'm catching up with is dependence on a particular DBMS.
Really it the fact forbids (within the limits of recommendations) to use the given functional (change of a DB - a possible course of events, but not prohibitive complexity task after all to rewrite the code is admissible from TSQL on PLSQL?)? Or is it to reduce the cost of switching to another DBMS in case of possible errors in the planned choice of the original DBMS?

Answer 1

Yes, it's normal on Oracle, in general, all business logic in the DBMS is done

Answer 2

Another answer: data correctness is not a concept of storage, but of business logic. By taking this judgment to the low level, you break a neat architecture.
The data is not correct or not by itself. And the criterion of correctness can easily change, while not affecting storage at all.

Answer 3

It's just that many web developers mastered the database by sight. What are the triggers?
Therefore, in order not to look like complete fools among those who know the database, they came up with such a trick that what they do not understand is shit code.

Answer 4

Trigger is "implicit work logic". It adds difficulty in parsing the logic of the database, like a GOTO statement.
Well, any exception in the trigger will cause an immediate rollback of the action to which it was hung.

Answer 5

And now, for accuracy, let all the respondents also specify what projects she is working on.

Answer 6

They interfere with the localization of business logic within your application. Those. business logic is divided into two parts and into two programming languages. Let's consider each of the shortcomings separately.
1. Business logic is divided into two parts. This disadvantage is especially evident when using web frameworks and in particular the ORM pattern. In the ORM pattern, when creating, updating, deleting an object, event handlers are called through the event bus, business logic is written in them, and stored procedures are deprived of this. Stored procedures provide optimization, and programmers have the disadvantage of optimizing prematurely, which can lead to drastically complicated architecture and code.
2. Business logic is written in two programming languages. First, you need to know, know well, two languages. Secondly, you need to introduce Coding Style Standards for one more language. Thirdly, stored procedure languages ​​(T-SQL, PLSQL, etc.) are not very suitable for writing complex business logic, and modern requirements for business logic just require sophistry. In general, the code turns out to be confusing, unreadable, difficult to modify, etc.
I only recognize triggers, only util triggers. For example, a trigger that prohibits the deletion of records in a certain table can be done using rights, but it is better to additionally protect it with a trigger that will be distributed globally to all users. The second example is a trigger for counting the number of records in a table, as you know, COUNT (*) on large tables bypasses the entire index by PRIMARY KEY, and in general large tables are evil - it's better to shard right away.
PS From experience, I will say that I came across the KIS UZ Modus system, the developer refused to develop the server and washed down all the business logic in T-SQL and called them from the client desktop application. In general, the system was sad.

Answer 7

because:
- It is difficult to make changes (the procedure has changed - and the trigger on the table works according to the old logic)
- It is difficult to maintain (updated the table and a set of triggers worked and various documents appear as a result of updating the directory)
- the system logic should be in one place and not stuffed in various corners (updating the system becomes more difficult)
- Unnecessary difficulties in debugging (correct operation of the procedure or correct access to the database causes an error - because the trigger may not work correctly)
- and all these problems are squared when one trigger causes another trigger to fire on another table!

Answer 8

There is the concept of a two-link system and a three- link system .
So in a two-link it is a masthead. And in a three-link base should be as blunt as possible.
And the Web is a three-tier architecture a priori.

Answer 9

They try to assemble all the logic in the application, they don’t even write long htaccess, but do routing at the application level. Firstly, if a third-party developer works with the application, he does not see the trigger, and secondly, he changed something, and the trigger still works if they did not know about it or simply forgot to remove it.
And if you have almost no application (or it is minimal), but a purely data processing server, then please ...

Answer 10

I apologize for the wrong terminology, but:
If we take the "layers" of a Web application, then why can't we make the following distribution and put it in the DBMS
Model - (DBMS - table, view + possibly stored_procedures, triggers)
Controller - (DBMS - stored_procedures, functions, triggers)
and leave the "layers" Route and View for web programming.
PS After all, freeware (and other licensing schemes) DBMS (mysql ...) have these mechanisms and it's a sin not to use them...

Answer 11

This is about as bad as global variables and code copy-paste put together.
From copy-paste, there is duplication of code: in the business logic and in the repository.
From global variables - the impossibility of tracking changes.
Dependence on a specific DBMS is a mere trifle and not a minus at all compared to the shortcomings that I have listed.