What is better trigger or procedure? And why not shove all the logic into procedures?

Question

MySQL

What is better trigger or procedure? And why not shove all the logic into procedures?

Answer 1

What is better trigger or procedure?

The inserted and deleted tables are not passed to the procedure, they require an explicit call. And in triggers some operations are forbidden.

And why not shove all the logic into procedures?

It is possible, some do: it is easier to maintain (DBA has nothing to do with programming procedures - administrator ); in general, procedures are better cached, etc.

Answer 2

What is better trigger or procedure?

Each task has its own tool, each tool has its own task.

And why not shove all the logic into procedures?

• Not everything can be done with procedures.
  
• Not everything is optimally done by procedures.
  
• Procedures can be harder to maintain - DBA time is usually more expensive than developer time. Yes, and the requirements for the level are much lower in the case of logic in the code - you can entrust a junior to change some simple algorithm, but he is unlikely to be allowed to change the database schema.
  
• Changing procedures in some DBMS can lock up the entire database for a long time.
  

Answer 3

triggers / procedures are fraught with surprises during replications,
in general, therefore they are not recommended to be used at all
; they are there in order to be able to do something necessary (checks there, transactions, etc., according to the black box principle), but in the general case The database should remain a database - a reliable repository of ordered data with a predictable access time

Answer 4

Oracle programmers are scratching their heads - "what, can it be done differently?" :)
Trigger - called by the database automatically on an event - adding, deleting a record, changing a record. It is used to ensure the integrity of the database, programming the response to an event - for example, when adding a payment to a subscriber, automatically recalculate his balance and allow access to the Internet (operator's billing database), or automatically calculate bonus points when adding a payment.
Procedure - called from outside the database by a programmer, often looks like a table access or something similar. For example, a daily calculation of the traffic consumed by all subscribers can be drawn up as a procedure.
Thus, the fundamental difference is who calls for execution - the database itself (trigger), or the programmer from the outside (procedure). No one bothers to write a procedure that, when adding a payment, will calculate bonuses, recalculate the subscriber's balance, allow / deny access to the Internet, and call this procedure from the trigger for adding / changing / deleting table entries. This way you can save code - write it once. It will be easier to maintain this database in the future.
Implementing application logic on triggers/procedures is, in principle, the right approach when all data processing details are collected in one place, in many cases this will simplify working with this database. For example, in the case of the same billing, it is enough just to add a payment record to the database and all the necessary actions will be performed. Those. the development of the billing web interface is simplified - there is no need to track dependencies between tables at the application level, recalculate dependent fields, etc.
In large database servers, there is support for triggers and procedures, the same oracle is a vivid example. To write procedures, there is a language that allows you to implement almost everything. And maybe that's all - I'm not an oraclist. In mysql, triggers and procedures appeared relatively recently, and developers working with it are just used to implementing everything on the application side.

Answer 5

It's exactly like with screwdrivers, hammers, nail pullers, files - they are all designed for different actions. Sometimes, of course, it turns out to drive a self-tapping screw for metal into drywall with a file, but it doesn’t hold up well. Which does not mean at all that drywall is a bad material or self-tapping metal screws are bad ... all the more you can’t blame the file ...
The same with triggers, procedures and business logic on the application side - if used appropriately and correctly, these will be excellent designs, otherwise - it will be kaka.
p/s/ and yes, triggers are not only DML, but also DDL of different levels + all sorts of specific types like event_driven, logon, etc.

Answer 6

The question is what exactly do you want to do. After all, the trigger and procedure have their own technological focus

Answer 7

And why not shove all the logic into procedures?

The complexity of maintenance and, accordingly, the time and cost will increase greatly.

Answer 8

I'll add my 5 cents.
At one time (90 years, I didn’t know before), having opened any book on designing applications and databases, you would have read just such a recipe - all logic is in DB. on procedures and triggers, and the client part - in fact, only provides an interface with the user. This is a client-server architecture.
Now, with the transition to the web, the architecture of the application that works with the database has become three-tier, so it has become possible to move the logic from the database to the level of the web server / application server. This is neither bad nor good. It's just so fashionable now - the database is just a data store, and the logic is all on the application server. It used to be fashionable to keep the logic in the database.
Although no one had previously prevented making a three-link, it turned out to be artificial and additional arguments were needed to implement it. Now you don’t need to create a three-link - it already exists due to the presence of a web application.
Those who do not develop web applications are simply following the trend.
According to the mind, you need to combine both approaches, because. some operations are best implemented in the database, and some in the application server or in the client side.
Well, the question of price, of course - a good DBA (or database programmer) costs more (and these are rarer animals) than a good web developer.

Answer 9

Why doesn't anyone suggest writing procedures first and then using them in triggers?
In general, start with their procedures somehow easier to debug, and then shove them into triggers.