Предыдущая лекция |   | Следующая лекция
:----------------:|:----------:|:----------------:
[Представления (View)](./sql_view.md) | [Содержание](../readme.md#проектирование-баз-данных) | [Создание подключения к БД MySQL. Получение данных с сервера.](./cs_mysql_connection3.md)


# Хранимые процедуры. Триггеры.

## Хранимые процедуры

**Хранимая процедура** - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.

**За**

- Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
- Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
- Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
- Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

**Против**

- Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
- Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
- Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
- Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

**Ограничитель (Delimiter)** - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. В обычных запросах в качестве ограничителя используется символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую символ `|` в качестве ограничителя.

## Триггеры

<!-- https://www.digitalocean.com/community/tutorials/how-to-manage-and-use-mysql-database-triggers-on-ubuntu-18-04-ru -->

**Триггер** — это хранимая откомпилированная SQL-процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события внутри базы данных (вставки, удаления, обновления записей).

Хранимые процедуры запускают во всех средах, и нет необходимости перестроения логики. С того момента как вы создали хранимую процедуру, не важно какое приложение вы используете для вызова процедуры. Также не важно на каком языке вы программируете, логика процедуры содержится на сервере БД.

Также хранимые процедуры могут сократить сетевой трафик. Сложные, повторяющиеся задачи можно обрабатывать с помощью процедур на сервере Баз данных, без необходимости отсылки промежуточных результатов приложению.

Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.

Общий вид синтаксиса для создания тригера:

```sql
CREATE TRIGGER 
    trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body
```

где:

- *trigger_name* - название триггера (уникальное в пределах БД);
- *trigger_time* - время срабатывания триггера. **BEFORE** — перед событием. **AFTER** — после события;
- *trigger_event* - событие:
    * *insert* — событие возбуждается операторами *insert*, *data load*, *replace*;
    * *update* — событие возбуждается оператором *update*;
    * *delete* — событие возбуждается операторами *delete*, *replace*. Операторы *DROPTABLE* и *TRUNCATE* не активируют выполнение триггера;

    Важно понимать, что *trigger_event* не представляет литеральный тип инструкции SQL, которая активизирует триггер. Например, триггер INSERT активизируется не только инструкцией INSERT, но и LOAD DATA, потому что обе инструкции вставляют строки в таблицу.

- *tbl_name* — название таблицы;
- *trigger_body* — выражение, которое выполняется при активации триггера. Если Вы хотите выполнять много инструкций, используйте операторную конструкцию `BEGIN ... END`. Это также дает возможность Вам использовать те же самые инструкции, которые являются допустимыми внутри сохраненных подпрограмм.

Не может быть двух триггеров для данной таблицы, которые имеют те же самые время действия и событие. Например, Вы не можете иметь два триггера BEFORE UPDATE для таблицы. Но Вы можете иметь BEFORE UPDATE и BEFORE INSERT или BEFORE UPDATE и AFTER UPDATE.

Триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм "обновляемого представления".

Например: создадим две таблицы **test** и **log**, напишем триггер, который после добавления записи в таблицу **test** будет вести лог этого события:

```sql
-- таблица, за которой мы будем следить
CREATE TABLE `test` (
    `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `content` TEXT NOT NULL
);

-- лог
CREATE TABLE `log` (
    `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `msg` VARCHAR( 255 ) NOT NULL,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `row_id` INT( 11 ) NOT NULL
);

-- триггер
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
   INSERT INTO log Set msg = 'insert', row_id = NEW.id;
END;
|
```

Здесь оператор DELIMITER служит для определения литерала начала/окончания процедуры и может состоять более, чем из одного символа (необходимо выбирать разделитель, который не будет использоваться в процедуре).

На столбцы таблицы, к которой привязан триггер можно ссылаться с помощью псевдонимов **OLD** и **NEW**. `OLD.col_name` указывает на столбец с именем *col_name* **до** изменения или удаления данных. `NEW.col_name` относится к колонке новой строки после вставки или существующей - сразу после её обновления.

Триггеры имеют несколько важных особенностей использования:

- триггеры могут создаваться только пользователем с привилегией SUPER;
- при использовании запроса, затрагивающего N - записей, триггер будет запускаться N - раз;
- после удаления таблицы, СУБД MySQL автоматически удаляет привязанные к ней триггеры.

```sql
create trigger
sale_item
before insert
on sales
for each row
 	update warehouse 
        set quantity=quantity - NEW.quantity 
        where id=NEW.item_id; 
```

```sql
delimiter $$
CREATE trigger 
update_warehouse
before update 
on warehouse 
for EACH row begin
	if NEW.quantity<0 THEN 
		SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'На складе недостаточно товара';
	END if;
end;
$$
```

Предыдущая лекция | &nbsp; | Следующая лекция
:----------------:|:----------:|:----------------:
[Представления (View)](./sql_view.md) | [Содержание](../readme.md#проектирование-баз-данных) | [Создание подключения к БД MySQL. Получение данных с сервера.](./cs_mysql_connection3.md)