- Хранимые процедуры: что это и как они работают
- Когда использовать хранимые процедуры
- Триггеры: что это и как они работают
- Когда использовать триггеры
- Заключение
Первую реляционную систему управления базами данных (RDBMS) разработали в IBM, она называлась System R. Работу над ней начали в 1974 году, а прототип завершили в 1977. System R стала первой реализацией реляционной модели баз данных (БД), которую предложил Эдгар Ф. Кодд. Он описал ее в своей знаменитой статье «A Relational Model of Data for Large Shared Data Banks», которую опубликовали в 1970 году. System R ввела многие концепции, которые стали стандартом для современных реляционных СУБД, включая язык SQL (Structured Query Language). Изначально его называли SEQUEL (Structured English Query Language).
Современный ANSI SQL сделал большой скачок вперед по сравнению с первой его реализацией, и это развитие все еще продолжается. Одной из важных новаций стало расширение возможностей управления базами данных, включая появление хранимых процедур и триггеров. Хотя это и не новшества, которые ввел непосредственно стандарт ANSI SQL, их внедрение в различные системы управления базами данных значительно улучшило производительность и безопасность работы с информацией. Довольно быстро стало понятно, что обработка данных только на стороне клиента БД — это очень непроизводительное расходование вычислительных ресурсов, которые были дорогими в начале последней четверти прошлого века. Поэтому разработали специальные инструменты, которые позволяют часть обработки информации перенести на саму систему управления.
Хранимые процедуры: что это и как они работают
SQL-базы данных должны реализовывать принцип ACID, поэтому сложную логику лучше упаковывать в транзакцию из нескольких инструкций и переиспользовать их, вызывать по имени. Собственно, хранимые процедуры — это и есть предварительно скомпилированный набор SQL-команд, который хранят в базе данных и выполняют на сервере по запросу. Они позволяют автоматизировать и оптимизировать операции с информацией, выполняют сложные задачи быстрее и безопаснее. Дополнительные выгоды возникают за счет того, что между клиентом и сервером не происходит лишней коммуникации.
Еще одно преимущество — безопасность. Пользователи получают доступ только к процедуре, а не к самой SQL-таблице, что снижает риск несанкционированных изменений. С другой стороны, процедура скрывает внутреннюю структуру данных и позволяет программистам БД изменять ее по необходимости без риска «сломать» работу бэкенда.
Давайте рассмотрим задачу обработки заказов. В этой ситуации нужна не только проверка данных, но и выполнение нескольких последовательных операций. У нас есть таблицы orders и order_items. При добавлении нового заказа нужно обновить данные в нескольких таблицах, проверить наличие товара на складе и уменьшить количество.
Создадим таблицы:
Теперь создадим хранимую процедуру, которая выполняет все нужные операции при добавлении нового заказа:
Процедура записывает в таблицу очередной заказ, а далее проверяет, что на складе это все есть. Если чего-то не хватает, то процедура выбрасывает исключение, транзакция откатывается.
Когда использовать хранимые процедуры
Использовать хранимые процедуры стоит для любого взаимодействия с базой данных. Изоляция ее структуры от бизнес-логики — это очень ценное свойство, которое повышает гибкость вашей команды. Поэтому ответим на этот вопрос от обратного и упомянем случаи, когда хранимые процедуры использовать будет не такой уж хорошей идеей:
- В кросс-платформенных приложениях, где persistence могут обеспечивать разные базы данных. Конечно, есть ANSI SQL, но каждый производитель реализовал свои диалекты, которые между собой не очень хорошо совместимы. Даже между версиями системы одного производителя бывают так называемые breaking changes, после которых требуется доработка кода базы.
То есть даже если в MySQL и в PostgreSQL есть хранимые процедуры, вызывать их единообразно скорее всего не получится. Выходом из ситуации может быть использование специальной обертки на уровне бизнес-логики, которая изолирует драйвер базы данных. - Если бизнес-логика очень часто меняется. В этом случае хранимые процедуры не очень помогают, потому что все постоянно переписывается. Поэтому на время стабилизации логики можно пользоваться прямыми вызовами. А после этого абстрагировать бэкенд от структуры данных с помощью процедур.
- Не стоит в процедуры упаковывать логику, которая не касается поддержания согласованности информации. Например, локализацию сути ошибки, которая возникла в базе, лучше передать наружу: бэку или даже фронту. А из базы отдать только то, что непосредственно связано с возникновением неполадки.
То есть даже если в MySQL и в PostgreSQL есть хранимые процедуры, вызывать их единообразно скорее всего не получится. Выходом из ситуации может быть использование специальной обертки на уровне бизнес-логики, которая изолирует драйвер базы данных.
И последнее, команде проекта стоит трезво оценивать свои силы. Если у инженеров нет достаточно сильного и опытного базиста, то очень легко превратить даже маленькую базу в большой источник проблем, которые трудно отслеживать.
Триггеры: что это и как они работают
Триггеры — это те же самые хранимые процедуры с той лишь разницей, что они автоматически выполняются в ответ на определенные события: вставка, обновление или удаление записей в таблице. Такая событийно-ориентированная модель позволяет создавать надежные механизмы обработки изменений в БД — без необходимости обвешивать бизнес-логику или другие хранимые процедуры условиями «если произошло такое-то изменение данных, сделай то-то».
Триггеры позволяют реализовать логику, которая будет выполняться при каждом изменении данных, независимо от того, какое приложение вносит изменения. Это повышает уровень контроля и безопасности информации, так как вся логика сосредоточена в одном месте — на сервере БД.
Когда использовать триггеры
Триггеры особенно полезны для автоматизации и обеспечения целостности данных и уместны для выполнения простых, одношаговых проверок и обновлений. Допустим, мы хотим, чтобы в примере выше при создании/обновлении заказа обязательно было указано время последней транзакции. Доработаем сначала таблицу:
Теперь напишем функцию, которая будет этот столбец обновлять — напрямую вызывать код в триггере не получится:
А теперь создаем триггер:
Теперь при создании/обновлении заказа будет вызываться функция установки последней даты и времени его изменения. Может возникнуть вопрос, почему мы не положили в триггер контроль остатков на складе из предыдущего примера, а оставили его в хранимой процедуре. Все дело в том, что сложную логику лучше оставлять в процедуре, а триггеры использовать для очень простых действий, как в примере с автоматической записью времени последнего обновления транзакции. Иначе ваша база данных будет обрастать неявной логикой, сложность которой нарастает очень быстро, и поведение базы может стать плохо предсказуемым. Поэтому в триггеры ставим только очень простые действия.
Еще одна область, где этот вид процедур эффективен, — поддержание целостности данных между связанными таблицами. Например, при удалении записи из departments триггер может автоматически удалять или обновлять все связанные записи в employees. Это предотвращает возникновение «осиротевших» (orphaned) записей и поддерживает согласованность данных.
Также триггеры полезны для автоматического ведения журналов изменений. В системе учета он может записывать каждое изменение в отдельную таблицу логов. Это позволяет изящно выстроить систему аудита.
Однако триггеры не подходят для сложных бизнес-операций, которые включают несколько шагов или требуют взаимодействия с внешними системами. Например, в случае обработки заказов, где нужно проверить наличие товаров, обновить запасы, обработать платеж и отправить уведомление клиенту, лучше использовать хранимые процедуры.
Заключение
Хранимые процедуры и триггеры стали важным строительным элементом БД. Они позволяют упрощать взаимодействие клиента и сервера, ускорять обработку данных, централизовать логику и, главное, обеспечивать целостность информации. Триггеры и процедуры схожи по поведению, но случаи их применения различаются. Первые нужны для автоматических простых действий, тогда как последние лучше подходят для вызываемых клиентом базы данных сложных последовательностей инструкций.
