Вопрос по статье на Хабре про триггеры — могут ли гарантировать триггеры целостность данных?

Question

[footballer]

Вопрос по статье "Вы не любите триггеры?" https://habrahabr.ru/post/310040/

Там приводится пример "легкого" триггера:

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

Мне не очень понятно:
1) этот триггер, я так понимаю, повешен автором на событие AFTER INSERT таблицы operations. А current_amount - это кол-во, которое мы инсертим в таблицу operations. И автор внутри триггера цепляется к переменной current_amount, но как переменная current_amount попадет внутрь триггера? Или предполагается, что автор выше достал заинсерченную строку из таблицы inserted и присвоил в current_amount (то есть, автор просто опустил этот кусок кода)?

2) Делая триггер на вставку в таблицу operations, мы хотим, чтобы вспомогательная таблица totals обновлялась всегда, когда приложение шлет запрос на вставку в таблицу operations, так мы хотим обеспечить целостность. Но получается, что стороннее приложение по ошибке или по взлому может послать запрос на апдейт\вставку напрямую в таблицу totals, и тогда целостность данных все-равно нарушается. Т.к. таблица totals - вспомогательная, то мы должны разрешить вызывать инсерты\апдейты в эту таблицу только из нашего триггера (ну либо из процедур на стороне sql-сервера, и должны запретить делать там изменения запросами от сторонних приложений). Для этого нам нужно либо скрыть эту таблицу из схемы данных (чтобы она не была видна наружу, но была видна внутри sql-сервера), либо ограничить инсерты\апдейты (разрешив их только вызовами изнутри sql-сервера), но мне кажется, что sql-сервер не позволяет добавлять такие ограничения на таблицы (или я не прав?). Если я прав, то смысл нам писать триггер, обеспечивающий целостность, если хакерское приложение все-равно может нарушить целостность, вызвав напрямую апдейт\инсерт в таблицу totals?

Answer 1

[Melkij]

но как переменная current_amount попадет внутрь триггера?

Автор статьи не стал вдаваться в детали какой-либо СУБД, поэтому уточняйте в документации к своей СУБД.
Для for each row триггеров обычно можно читать из NEW.amount в after insert триггерах.
Для for each statement - смотрите документацию к своей СУБД.

либо ограничить инсерты\апдейты (разрешив их только вызовами изнутри sql-сервера), но мне кажется, что sql-сервер не позволяет добавлять такие ограничения на таблицы

Смотрите опять же документацию к своей СУБД.
Для postgresql такую модель прав сделать можно. Для этого необходим отдельный пользователь для деплоя приложения (вообще полезная штука всегда) и security definer триггер.

могут ли гарантировать триггеры целостность данных?

Могут, но бывают сюрпризы. Например, mysql вам не запретит из триггера на ACID табличке что-то делать с myisam. Последующий rollback транзакции по любой причине = у вас потеря целостности. Впрочем, если вы знаете о целостности данных - то вы и не станете использовать всякое myisam.

Answer 2

[Denis Popov]

А зачем для этого триггеры? Для обеспечения целостности существуют внешние ключи (Foreign Keys)

Comments

[footballer]

В статье же написано, для чего.

Кратко - нужно создать вспомогательную таблицу totals для хранения итоговых сумм на счетах. При добавлении новой суммы к счету (инсерт в таблицу operations), мы должны обновлять итоговую сумму в таблице totals для этого счета. Таблица totals создана для того, чтобы иметь возможность быстро узнать итоговую сумму на счете, не складывая в цикле суммы всех подряд операций на счете из таблицы operations (это медленно).

[Denis Popov]

footballer, Понятно. Но лучше сделать это на сервере, а не средствами СУБД имхо, два запроса при добавлении суммы. Все эти триггеры, представления, процедуры и т.п. очень сильно влияют на производительность... Т.к. триггер вешается только на одну операцию, их придется дублировать на инсерты, апдейты и тп... Триггеры, представления и тп конечно удобные вещи, но жертва велика. К примеру прямой запрос навороченный займет 0.004с, а с представлением это уже может быть уже 0.15с

[Melkij]

Denis Popov, или вы говорите про какую-то одну конкретную субд или сильно заблуждаетесь.

триггер вешается только на одну операцию, их придется дублировать на инсерты, апдейты

Зависит от СУБД. postgresql не проблема повесить один триггер на пачку операций.

Все эти триггеры, представления, процедуры и т.п. очень сильно влияют на производительность...

Подчас весьма положительно влияют. Т.к. не тратим время на сеть и плюс СУБД может кэшировать план хранимок.

К примеру прямой запрос навороченный займет 0.004с, а с представлением это уже может быть уже 0.15с

it depends. Запрос к таблице и запрос к вьюхе может быть переписан в идентичный вид планировщиком. Но вьюхи вешаются не для производительности, а для удобства. Для производительности - материализованные вьюхи.

[footballer]

Denis Popov,

Но лучше сделать это на сервере, а не средствами СУБД имхо, два запроса при добавлении суммы.

С точки зрения логики как раз кажется логичнее, чтобы сама SQL-база себя контролировала, а не полагалась на то, что сервер приложения выполнит оба запроса и не забудет про второй. Т.к. я всегда пытаюсь найти логически правильные вещи, то я задумался над тем, что писать триггеры правильно (хотя все пишут, что это замедляет выполнение и т.п., но мне не понятно, как апдейт таблицы totals из триггера может быть медленнее, чем такой же апдейт этой же таблицы из кода приложения, апдейт же в итоге абсолютно одинаковый получается в обоих случаях, разве что в случае с триггером сначала сумму апдейта нужно будет достать из таблицы inserted, но мне кажется, достать запись из этой таблицы не может быть долго). В общем, единственное, что пока меня смущает, так это то, что даже если я в Ms SQL сервере сделаю вспомогательную таблицу totals и повешу триггер AFTER INSERT на таблицу operations, то все-равно сервер приложения может напрямую вызвать изменение данных в таблице totals и нарушить целостность (потому что, насколько я знаю, в MS SQL нельзя скрыть таблицу от внешнего кода, либо я не знаю способ и хотелось бы узнать).

Answer 3

[Boris Köln]

могут ли гарантировать триггеры целостность данных?

В зависимости от уровня изолированности транзакций.