What is the best way to develop server software? On external scripts or on triggers?

Question

SQL

Good afternoon!

Currently, there is a system of mobile clients and server software that ensures their interaction with the database, as well as the formation of user information.

Now I am faced with the task of significant expansion, and most likely, reengineering of existing software. And I need to decide in principle which way to go. In general, I see three ways:

1. Traditional, when software is developed in the form of java/php/perl scripts, it doesn't matter.
2. Move a significant part of the logic (there are a lot of small settlement operations) into triggers.
3. Transfer a significant part of the logic to stored procedures and call them either from external scripts or from triggers.

What attracts me to the second way? Many things, such as logging, calculating forecasts and trends, are more convenient for tracking event changes.

What scares? a lot of things - debugging issues, errors in the trigger will lead to an error in the transaction as a whole, and so on. Their great unpredictability. It is not clear how to update all triggers on replicated tables.

Already broke his head. I think the collective mind will be more effective than my one. :) Maybe someone has experience in implementing software on triggers?

Answer 1

If you will be the only software developer, I recommend not choosing the path with triggers and implementing logic on the database side. Since you will create a lot of problems for yourself, and you will not feel the advantages of this method.
The development of all or part of the logic on the database side is convenient in cases where it is necessary to 'divide and conquer', when it is convenient, for reasons of reliability of development and minimization of errors (for example, violations of the logical structure of the database), to highlight and arrange the appropriate code on the database side in such a way that the logic database structures could not be broken by any errors on the application side (php/java/..). Actually, initially this function was assigned to the code on the side of the database - control over the integrity of the database.
It's just that it's often not so easy to isolate a part of the logic that can be 'beautifully' implemented on triggers and stored procedures. For example, everything related to the interface (or API request formats) is stupid to implement on stored procedures, but methods that, for example, implement operations with user accounts (such as transfer transactions between users), are better implemented inside the database as a stored procedure.
And of course, server applications have a huge number of ready-made solutions, libraries, frameworks to choose from ... which may simply not be available for the stored procedure language of your database.

Answer 2

And if the customer wants to change the database, what will you tell him? “Excuse me, it takes a couple of man-months to adapt triggers”? :)
Triggers and stored procedures are hell and sodomy in matters of support and development.

Answer 3

The traditional way is not by chance traditional. You need a good reason not to use it.
Development in the database, even after installing / configuring / adapting the environment for development, is still longer than the development of conventional programs. There are also additional architectural restrictions. The only plus is the increase in the speed of complex selections.

Answer 4

I mainly consider triggers as crutches when upgrading an already finished system - for example, adding an audit of data changes, updating fields in FK tables in case of deviations from the normal form for the sake of performance, etc. Initially, I would not bet on them, except when, for example, iron protection is needed from inadequate actions of the critical data application layer (for example, deleting financial transactions of a certain type, which under no circumstances can be deleted). It is highly desirable to describe all the logic on triggers and apply this description to the database and do not forget to update it in the repository.
Now, about the holivar about placing business logic - in the database or in the application layer? As always, the best option in terms of performance is palliative. It is also the most expensive and difficult to maintain. But there is no other way - if you want to get the most out of the system - use complex structures. I try to unconditionally hide logic in SP if:
a) the operation is critical in terms of business integrity of key data
b) the operation produces or manipulates a lot of related data and/or requires specific database options (locks, partial rollbacks, try/catch, etc.) for efficient implementation.
c) only the back-end developer has knowledge about the business model and processes in the system being designed, and front-end developers are just form drawers. Sometimes it is faster, after collecting requirements from the customer, to implement an armor-piercing backend based on them, than to tell the others how it should work, and then debug everything in terms of functionality and performance by the whole world.
In any case, it would be nice to describe the logic partially implemented in the database and put the description in the repository. Scripts testing the logic are also desirable.

Answer 5

Not so long ago I read a srach about this topic here

Answer 6

Use a combined approach. Where it is more convenient 1 - then 1, where 2 - then 2, etc. Try to do it in priority 2. Don't get paranoid and don't try to shift everything to the base.
The second approach is logically correct. But very often 1 approach is much more profitable in terms of development time or resource intensity.