How to refer to tables and fields of these tables in triggers?

Question

SQL

Hello.
I want to solve a specific problem:
The Debt table in the Amount field contains the value of the total debt of the counterparty The Payment table in the Amount
field contains the value of the payment amount from the counterparty
payment. And at the same time I want to understand the principle of references from the trigger to other tables. Help the layman, please

Answer 1

First, as Sumor already said , you need to think first of all about the transactionality of the operation, your task is to reduce the value of the Debt when making a payment - a classic case of making several changes within the same transaction. So this operation can be performed at the application level as well. There can be some troubles with the trigger - for example, if you subsequently go to delete some of the payment records or reload them without changing the debt, then the trigger will have to be disabled. Or as Sumor said, you can turn them off by accident and not notice. With money, such situations must be avoided even when designing. In general, of course, triggers are a perfectly acceptable way to update calculated data (debt is just such a value), but I think in applications that are critical to data accuracy, it is better to refrain from them.
Or stored procedures will suit you - then the logic of changing the debt and adding a payment will be more obvious, and at the database level. Then you will need to restrict access to the main tables so that no one accidentally changes them, and allow all changes only using storages. This approach is less scalable than application-level logic, but more reliable if you have a large project and many developers - the database will have its own security layer in this case.

Answer 2

Since it is not indicated which DBMS we are talking about, I will say about postgresql and mysql
www.postgresql.org/docs/9.4/static/plpgsql-trigger.html
https://dev.mysql.com/doc/refman/5.6/en/trigger -sy...
The manuals for both DBMSs show references to OLD/NEW and some data operations.

Answer 3

For SQLServer, these are the inserted and deleted tables Use of the inserted and deleted tables .
Consider using transactions instead of triggers. Especially if the action in the trigger is supposed to be canceled by some condition.
The trigger is separated from the logic of adding payment data, and it will not be obvious to someone studying your code why the Debt table has changed.
Triggers can be accidentally/on purpose/for debugging turned off and you get a lot of problems.

Answer 4

Do not break your head, do not write triggers.
Triggers are used when there is absolutely no other way out, with them comes a lot of problems and understanding - it was necessary to do it on storage.
You have a great way to avoid these thoughts, do it on storage in one transaction with error handling, the task is the simplest.