You don't like the triggers?

don't You like cats? Yes, you just don't know how to cook them! (C) Alf

When you design a enough volume relational databases often taken the decision to retreat from normal form — "denormalization".
The reasons may be different. From attempts to expedite access to certain data limitations of the underlying platform/framework/development tools and to the lack of skill of the developer/designer of the database.
However, strictly speaking, the reference to the limitations of the framework, etc. — in fact, an attempt to justify the lack of skill.

Denormalized data is a weak point, through which one can easily bring our base in inconsistentname (non-integrated) state.

What to do with it?

Example

In the database there is a table with some financial transactions: the receipt and withdrawal of funds on different accounts.
You should always know the balance on the account.

In the normalized data the balance — always expect value. Sum up all income minus depreciation.

However, when the number of operations well, very big, each time to calculate the remainder too expensive.
It was therefore decided to store the current balances in a separate table. How to update data in this table?

Solution "as usual"

Almost all information systems with which I had to work, this task was carried out by an external application, which implements the business logic. Well, if the application is simple and the points of change of data is one of the form in the user interface. And if there are any imports, API, third party applications and so on? And these things are done by different people, teams? But if not, one table with results and several of them in different ways? And if not, one table with transactions (met such)?

Here to follow what the developer when you refresh operations do not forgot to upgrade a bunch of tables, it becomes harder and harder. Data loses integrity. Balances on the account do not correspond to operations. Of course, testing should identify such situations. But we don't live in that perfect world.

the Cat Triggers

Alternatively, control the integrity of the data denormalized "adult" DBMS using triggers.
I also have heard that the triggers are terribly slow database, so their use is impractical.
The second argument was that the whole logic is in a separate app and keep the business logic in different places is also inappropriate.

Let's see.

Brakes

The trigger is executed inside a transaction, modifies data in the table. The transaction cannot be completed if the trigger has not made the necessary actions. The conclusion is that the triggers should be "easier". An example of a "heavy" query in the trigger:

the

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

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

The query with the database growth will eat up more and more time and resources. But the same result can be achieved using "light" type query:

the

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

Such a trigger when a new row is added will simply increase the total of the account, not expecting it again, it does not depend on the amount of data in the tables. To calculate the result, it makes no sense, so how can we be sure that the trigger ALWAYS fires when you add a new operation.

Similarly processed, deleting, and modifying rows. This type of triggers practically does not slow down operation, but will guarantee the coherence and celostnosti data.
Always, when I watched "brake" when you insert data into a table with a trigger, it was an example of such a "heavy" query. In most cases, managed to rewrite it in a "lightweight" style.

Business logic

Here it is necessary to separate flies from cutlets. It makes sense to distinguish between functions that ensure data integrity, from the actual business logic. In each case ask the question: if data was normalized, you would need such a function? If the answer is positive — this is business logic. Negative — data integrity. Safely wrap these functions in triggers.

However, it is believed that all the business logic is easy to implement by means of modern DBMS, such as PostgreSQL or Oracle. Confirmation is found in his just-for-fun project.

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

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

PS

Thanks for the reasoned arguments "for" and "against."
The trail of discussion of this article, a few comments.
the

the
• In the article the author drew attention only on one aspect of the use of triggers to draw attention to the use of such a powerful tool. The theme, of course, much wider.

the Approach described in the article, you can allow her to leave the indexes on the table operations that can speed up insertion of data into this table. On large volumes this effect is easy to compensate for including the time spent on a job trigger, not to mention the cost of memory for indices. the

• it is Important understand which tool for what use, then you will avoid many issues for example, statement restart BEFORE triggers
the
• log triggers — generally thanks

Article based on information from habrahabr.ru