Trigger in MySQL VS logic in PHP application?

You can not say which is better. It depends on the architecture of every solution, on the team's experience, on the goal, you can even say it's a taste.

Can you guarantee that you will put it into the app when it is needed? It is not difficult if the application is built in the right way. It is not only possible but desirable that in every application there is only one place to do the operations and use the established algorithm where you need it. In this way it is possible to create even different algorithms to execute in different circumstances. This is done with DRY . Particularly I prefer the application.

One way to have something canon is to use the trigger in the database. Not everyone likes it. If you have to do it conditionally, it can be more complicated. Can you guarantee that there will not be any situation where the trigger does not apply or should it apply differently?

Some people will say that there is competition control in the database. But you can do this in the application also in one transaction.

This is part of the business rules. You can read more at Business Rules in the Database - what are the advantages and disadvantages? . It's almost a duplicate.

Generally speaking, you avoid spreading business logic across different layers of the application. Imagine that other people will maintain the code, what will happen? The new programmer will have to shake a little to realize that the logic that updates the stock quantity is simply not in the code layer responsible for changing the stock, it is in the data layer.

When I studied software engineering, I had an interesting phrase that was used 'data layer is data / storage layer, business is business'. but then I realized that it's not that simple and that making the team maintain the conceptual integrity of a project is really difficult, requires discipline.

In this more specific case, the performance will certainly be something to be discussed, since removing the trigger will cause the application to have an access to the database to do the update (or other action) and this usually is slower.

You should look at the key factors that are going to affect your project, and what architectural decisions have been made at the outset so that one-time maintenance implementations will not destroy your architecture but will not destroy performance.

An article by Martin Fowler (a classic reference in OOP) can help you with topics already discussed, I removed a portion of the text that may give you a vision:

  

"With both of the first two, the database is used pretty much as a   storage mechanism. All we've done is asked for all the records from a   particular table with some very simple filtering. SQL is a very   powerful query language and can do much more than the simple filtering   that these examples use .... "

Something like: Generally, the database is a mechanism for storing data, all we do is request a dataset with a particular filter criteria. SQL is a powerful language and can do much more than simply filter ... - free translation.

This text shows the basic definition and goes into the discussion of the major aspects of using logic in the application or in SQL statements, is not so didactic and assumes that the reader has experience in certain scenarios, but will help. >

link

There is another, though not so discussed, many commercial applications support more than one database (Oracle, Mysql, SQL Server, etc.), the "SQL language" that each supports will vary by vendor, as well as features that can be used in conjunction with the language chosen for the other layers (PHP, ASP.NET, etc.), so putting the business rules in the database will require a more complex product maintenance strategy for various databases , since the main validations of a software are the business rules and this cost can directly impact the product management model.