You do not like triggers?

You do not like cats? Yes, you just do not know how to cook them! (c) Alf

When designing enough voluminous relational databases, it is often decided to deviate from the normal form - “denormalization”.
The reasons may be different. From an attempt to speed up access to certain data, the limitations of the platform / framework used / development tools and to the lack of qualifications of the database developer / designer.
However, strictly speaking, a reference to the limitations of framework, etc. - essentially an attempt to justify the lack of qualifications.

Denormalized data is a weak link through which we can easily bring our database to a non-consistent (non-integral) state.

What to do with it?

Example

In the database there is a table with some financial operations: receipt and withdrawal of funds for different accounts.
You need to always know the balance of the account.
')
In the normalized data, the balance is always a calculated value. We summarize all receipts minus write-offs.

However, when the number of operations is very large, each time it is too expensive to calculate the balance.
Therefore, it was decided to keep the current balances in a separate table. How to update the data in this table?

“As usual” solution

Practically in all information systems with which I had to work, this task was performed by an external application in which business logic was implemented. Well, if the application is simple and there are only one data change points, from the form in the user interface. And if there are any imports, APIs, third-party applications, and so on? And these things do different people, teams? And if there is not one table with the results, but there are several of them in different sections? And if more than one table with operations (met and such)?

Here to keep track of the fact that the developer, while updating the operation, did not forget to update a handful of tables, it becomes more and more difficult. Data loses integrity. Account balances do not correspond to transactions. Of course, testing should reveal such situations. But we do not live in such an ideal world.

Cats Triggers

As an alternative, triggers are used to control the integrity of a denormalized data in an “adult” DBMS.
We often heard that triggers terribly slow down the database, so their use is impractical.
The second argument was that all the logic is in a separate application and keeping business logic in different places is also inappropriate.

Let's figure it out.

Brakes

The trigger is triggered inside a transaction that changes the data in the table. A transaction cannot be completed unless the trigger has taken the necessary action. Hence the conclusion: triggers should be as easy as possible. An example of a "heavy" request in a trigger:

update totals set total = select sum(operations.amount) from operations where operations.account = current_account where totals.account = current_account 

The query refers to the operations table ( operations ) and summarizes all the amounts of operations ( amount ) for the account ( account ).

Such a query with the growth of the database will eat more and more time and resources. But the same result can be achieved using the “easy” query of the type:

 update totals set total = totals.total + current_amount where totals.account = current_account 

Such a trigger when adding a new line will simply increase the total for the account, without counting it again, it does not depend on the amount of data in the tables. There is no sense in re-counting the total, as we can be sure that the trigger ALWAYS work when a new operation is added.

Similarly, deletion and modification of rows is handled. Triggers of this type practically do not slow down operations, but will guarantee the coherence and integrity of data.

Whenever I observed “brakes” when inserting data into a table with a trigger, it was a sample of such a “heavy” query. And in most cases it was possible to rewrite it in the “easy” style.

Business logic

Here it is necessary to separate the flies from cutlets. It makes sense to distinguish between functions that ensure data integrity from the actual business logic. In each such case I ask the question: if the data were normalized, would such a function be necessary? If the answer is yes, this is business logic. Negative - ensuring data integrity. Feel free to wrap these functions in triggers.

However, it is believed that all business logic can be easily implemented using modern DBMS, such as PostgreSQL or Oracle. I find the confirmation in my just-for-fun project .

I hope this article will help reduce the number of bugs in your information system.

Of course, I am far from thinking that everything written here is the ultimate truth. In real life, of course, everything is more complicated. Therefore, decisions in each case to take you. Use your engineering thinking!

PS

Thank you for the reasoned arguments "for" and "against."
Following the discussion of the article a few comments.

• In the article, the author drew attention to only one aspect of the use of triggers in order to draw attention to the use of such a powerful tool. The subject, of course, is much broader .
• The approach described in the article may allow abandoning the indices on the operations table, which may speed up the insertion of data into this table. On large volumes, this effect easily compensates for the time costs of the trigger, not to mention the memory costs of the indices.
• It is important to understand which tool to use for what, then you will avoid many problems , for example, with statement restart in BEFORE triggers
• For logs, triggers are generally mast