Просмотр исходного текста страницы СУБД/Практические занятия №2(2 семестр)

== Триггеры '''({{Кр|My}}SQL)'''==
* https://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

<source lang="sql">CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }</source>

Триггеры это особые процедуры которые срабатывают при изменении данных в таблицы операторами '''INSERT, UPDATE и DELETE'''.

Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы. Получается, что триггер может быть в 6 состояниях.
<source lang="sql">	INSERT (BEFORE | AFTER)
	UPDATE (BEFORE | AFTER)
	DELETE (BEFORE | AFTER)</source>

== Что необходимо для работы (ограничения, не все)==

* '''Во-первых''', триггеры появились в {{Зел|MySQL 5.0.2.}} 
* '''Во-вторых''', триггеры в MySQL могут создаваться только пользователем с привилегией SUPER, т.е. если Ваше приложение использует триггеры, то ни на одном, даже платном, хостинге оно работать не будет!({{Зел|база для выполнения примеров и пользователь `студент` имеют эти права}}). 
** В качестве альтернативы, DBA может устанавливать переменную системы l'''og_bin_trust_function_creators в 1''', что позволяет любому со стандартной привилегией CREATE ROUTINE создавать сохраненные функции.
* '''В-третьих''', нельзя редактировать таблицу уже открытую для чтения или записи условием инициализации триггера. В этом случае просто используйте оператор SET для нужных полей. 

<source lang="sql">CREATE TRIGGER `artists_litera_autoupdate` AFTER UPDATE ON `cms_artists`
   FOR EACH ROW BEGIN
      UPDATE `cms_artists` SET `litera` = UPPER(SUBSTRING(NEW.`name`, 1, 1)) WHERE `id` = OLD.`id`;
   END;
</source>
 В противном случае Вы получите такую ошибку: 
 #1442 - Can't update table 'tablename' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
* Кроме того в показанном выше запросе есть ещё одна ошибка, которая после исправления предыдущей обязательно появится: 
 ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
* '''В-четвёртых''', значения надо менять до обновления. В конечном счёте правильный вариант: 

<source lang="sql">CREATE TRIGGER `artists_litera_autoupdate` BEFORE UPDATE ON `cms_artists`
 FOR EACH ROW BEGIN
    SET NEW.`litera`= UPPER(SUBSTRING(NEW.`name`, 1, 1));
 END;</source>

* '''В-пятых''', при очищении таблицы '''(TRUNCATE)''' триггер, повешенный на событие DELETE, исполняться не будет.
* В MySQL нет проблемы с рекурсивными триггерами! Разработчики MySQL просто лочат изменяемую таблицу даже на уровне триггера.
<source lang="sql">DELIMITER $$
create DEFINER=`root`@`%` trigger mytrigger
before insert on mytable
for each row
begin
	UPDATE mytable
	   SET a = 123
	WHERE (id=1);
end$$</source>
{{X|24px}} Выполняться не будет
* MySQL диалект триггеров несколько отличается от диалекта PostgreSQL:
** Не обязательно объявлять переменные в начале процедуры;
** Прервать выполнение триггера нельзя, он должен отработать полностью;
** Как было сказано выше, рекурсии мы не боимся, поэтому лишние проверки и дополнительные поля нам не потребуются.
* Поэтому несколько изменяется логика:
** Переменные появляются по ходу кода;
** Вместо возвратов из триггера, оборачиваем код условиями;
** Все изменения, касающиеся структуры дерева, применяем к вспомогательной объединенной таблице;
* {{V|24px}}В MySQL имеет значение порядок перечисления полей в запросе UPDATE, так как поля изменяемые во время запроса в самом же запросе меняют значение на новое, поэтому, если мы дальше по запросу используем эти поля в условиях, результат будет неадекватным.
{{Hider|Пример 1 (Создание записи)}}<source lang="sql">CREATE DEFINER = 'user'@'localhost' TRIGGER `ns_tree_before_ins_tr` BEFORE INSERT ON `ns_tree`
 FOR EACH ROW
BEGIN
 SET @left_key := 0;
 SET @level := 0;
-- Если мы указали родителя:
 IF NEW.parent_id IS NOT NULL AND NEW.parent_id > 0 THEN
 SELECT right_key, `level` + 1 INTO @left_key, @level
 FROM ns_tree
 WHERE id = NEW.parent_id AND tree = NEW.tree;
 END IF;
-- Если мы указали левый ключ:
 IF NEW.left_key IS NOT NULL AND NEW.left_key > 0 AND 
 (@left_key IS NULL OR @left_key = 0) THEN
 SELECT id, left_key, right_key, `level`, parent_id 
 INTO @tmp_id, @tmp_left_key, @tmp_right_key, @tmp_level, @tmp_parent_id
 FROM ns_tree
 WHERE tree = NEW.tree AND (left_key = NEW.left_key OR right_key = NEW.left_key);
 IF @tmp_left_key IS NOT NULL AND @tmp_left_key > 0 AND NEW.left_key = @tmp_left_key THEN
 SET NEW.parent_id := @tmp_parent_id;
 SET @left_key := NEW.left_key;
 SET @level := @tmp_level;
 ELSEIF @tmp_left_key IS NOT NULL AND @tmp_left_key > 0 AND NEW.left_key = @tmp_right_key THEN
 SET NEW.parent_id := @tmp_id;
 SET @left_key := NEW.left_key;
 SET @level := @tmp_level + 1;
 END IF;
 END IF;
-- Если родитель или левый ключ не указан, или мы ничего не нашли
 IF @left_key IS NULL OR @left_key = 0 THEN
 SELECT MAX(right_key) + 1 INTO @left_key
 FROM ns_tree
 WHERE tree = NEW.tree;
 IF @left_key IS NULL OR @left_key = 0 THEN
 SET @left_key := 1;
 END IF;
 SET @level := 0;
 SET NEW.parent_id := 0; 
 END IF;
-- Устанавливаем новые значения ключей
 SET NEW.left_key := @left_key;
 SET NEW.right_key := @left_key + 1;
 SET NEW.`level` := @level;
-- Формируем разрыв в дереве
 UPDATE _ns_tree
 SET left_key = CASE WHEN left_key >= @left_key 
 THEN left_key + 2 
 ELSE left_key + 0 
 END,
 right_key = right_key + 2
 WHERE tree = NEW.tree AND right_key >= @left_key;
END;

</source>{{Hider|end}}

{{Hider|Пример 2 (Изменение записи)}}<source lang="sql">
SQL код (4)CREATE DEFINER = 'user'@'localhost' TRIGGER `ns_tree_before_upd_tr` BEFORE UPDATE ON `ns_tree`
 FOR EACH ROW
BEGIN
-- Запрещаем изменять поля, или присылать гадости
 SET NEW.tree := OLD.tree;
 SET NEW.right_key := OLD.right_key;
 SET NEW.`level` := OLD.`level`;
 SET @return_flag := 0;
 IF NEW.parent_id IS NULL THEN SET NEW.parent_id := 0; END IF;
-- Проверяем, а есть ли изменения связанные со структурой дерева
 IF NEW.parent_id <> OLD.parent_id OR NEW.left_key <> OLD.left_key THEN
-- Дерево таки перестраиваем, что ж, приступим:
 SET @left_key := 0;
 SET @level := 0;
 SET @skew_tree := OLD.right_key - OLD.left_key + 1;
-- Определяем куда мы его переносим:
-- Если сменен parent_id:
 IF NEW.parent_id <> OLD.parent_id THEN
-- Если в подчинение другому злу:
 IF NEW.parent_id > 0 THEN
 SELECT right_key, level + 1
 INTO @left_key, @level
 FROM ns_tree
 WHERE id = NEW.parent_id AND tree = NEW.tree;
-- Иначе в корень дерева переносим:
 ELSE
 SELECT MAX(right_key) + 1 
 INTO @left_key
 FROM ns_tree
 WHERE tree = NEW.tree;
 SET @level := 0;
 END IF;
-- Если вдруг родитель в диапазоне перемещаемого узла, проверка:
 IF @left_key IS NOT NULL AND 
 @left_key > 0 AND
 @left_key > OLD.left_key AND
 @left_key <= OLD.right_key THEN
 SET NEW.parent_id := OLD.parent_id;
 SET NEW.left_key := OLD.left_key;
 SET @return_flag := 1;
 END IF;
 END IF;
-- Если не parent_id, то изменен left_key, или если изменение parent_id ничего не дало
 IF @left_key IS NULL OR @left_key = 0 THEN
 SELECT id, left_key, right_key, `level`, parent_id 
 INTO @tmp_id, @tmp_left_key, @tmp_right_key, @tmp_level, @tmp_parent_id
 FROM ns_tree
 WHERE tree = NEW.tree AND (right_key = NEW.left_key OR right_key = NEW.left_key - 1)
 LIMIT 1;
 IF @tmp_left_key IS NOT NULL AND 
 @tmp_left_key > 0 AND 
 NEW.left_key - 1 = @tmp_right_key THEN
 SET NEW.parent_id := @tmp_parent_id;
 SET @left_key := NEW.left_key;
 SET @level := @tmp_level;
 ELSEIF @tmp_left_key IS NOT NULL AND 
 @tmp_left_key > 0 AND 
 NEW.left_key = @tmp_right_key THEN
 SET NEW.parent_id := @tmp_id;
 SET @left_key := NEW.left_key;
 SET @level := @tmp_level + 1;
 ELSEIF NEW.left_key = 1 THEN
 SET NEW.parent_id := 0;
 SET @left_key := NEW.left_key;
 SET @level := 0;
 ELSE
 SET NEW.parent_id := OLD.parent_id;
 SET NEW.left_key := OLD.left_key;
 SET @return_flag = 1;
 END IF;
 END IF;
-- Теперь мы знаем куда мы перемещаем дерево
-- Проверяем а стоит ли это делать
 IF @return_flag IS NULL OR @return_flag = 0 THEN
 SET @skew_level := @level - OLD.`level`;
 IF @left_key > OLD.left_key THEN
-- Перемещение вверх по дереву
 SET @skew_edit := @left_key - OLD.left_key - @skew_tree;
 UPDATE _ns_tree
 SET left_key = CASE WHEN right_key <= OLD.right_key
 THEN left_key + @skew_edit
 ELSE CASE WHEN left_key > OLD.right_key
 THEN left_key - @skew_tree
 ELSE left_key
 END
 END,
 `level` = CASE WHEN right_key <= OLD.right_key 
 THEN `level` + @skew_level
 ELSE `level`
 END,
 right_key = CASE WHEN right_key <= OLD.right_key 
 THEN right_key + @skew_edit
 ELSE CASE WHEN right_key < @left_key
 THEN right_key - @skew_tree
 ELSE right_key
 END
 END
 WHERE tree = OLD.tree AND
 right_key > OLD.left_key AND
 left_key < @left_key AND
 id <> OLD.id;
 SET @left_key := @left_key - @skew_tree;
 ELSE
-- Перемещение вниз по дереву:
 SET @skew_edit := @left_key - OLD.left_key;
 UPDATE _ns_tree 
 SET
 right_key = CASE WHEN left_key >= OLD.left_key
 THEN right_key + @skew_edit
 ELSE CASE WHEN right_key < OLD.left_key
 THEN right_key + @skew_tree
 ELSE right_key
 END
 END,
 `level` = CASE WHEN left_key >= OLD.left_key
 THEN `level` + @skew_level
 ELSE `level`
 END,
 left_key = CASE WHEN left_key >= OLD.left_key
 THEN left_key + @skew_edit
 ELSE CASE WHEN left_key >= @left_key
 THEN left_key + @skew_tree
 ELSE left_key
 END
 END
 WHERE tree = OLD.tree AND
 right_key >= @left_key AND
 left_key < OLD.right_key AND
 id <> OLD.id;
 END IF;
-- Дерево перестроили, остался только наш текущий узел
 SET NEW.left_key := @left_key;
 SET NEW.`level` := @level;
 SET NEW.right_key := @left_key + @skew_tree - 1;
 END IF;
 END IF;
END;
</source>{{Hider|end}}
{{Hider|Пример 3 (Удаление записи)}}<source lang="sql">CREATE DEFINER = 'user'@'localhost' TRIGGER `ns_tree_before_del_tr` AFTER DELETE ON `ns_tree`
 FOR EACH ROW
BEGIN
-- Удаляем дочерние узлы:
 DELETE FROM _ns_tree
 WHERE 
 tree = OLD.tree AND
 left_key > OLD.left_key AND
 right_key < OLD.right_key;
-- Убираем разрыв в ключах:
 SET @skew_tree := OLD.right_key - OLD.left_key + 1;
 UPDATE _ns_tree
 SET left_key = CASE WHEN left_key > OLD.left_key
 THEN left_key - @skew_tree
 ELSE left_key
 END,
 right_key = right_key - @skew_tree
 WHERE right_key > OLD.right_key AND
 tree = OLD.tree AND
 id <> OLD.id;
END;</source>{{Hider|end}}
{{Hider|Пример 4 (Удаление узла со смещением дочерних узлов на уровень в вверх)}}<source lang="sql">CREATE DEFINER = 'user'@'localhost' TRIGGER `ns_tree_before_del_tr` AFTER DELETE ON `ns_tree`
 FOR EACH ROW
BEGIN
-- Убираем разрыв в ключах:
 UPDATE _ns_tree
 SET left_key = CASE WHEN left_key < OLD.left_key
 THEN left_key
 ELSE CASE WHEN right_key < OLD.right_key
 THEN left_key - 1 
 ELSE left_key - 2
 END
 END,
 parent_id = CASE WHEN right_key < OLD.right_key AND `level` = OLD.level + 1
 THEN OLD.parent_id
 ELSE parent_id
 END,
 `level` = CASE WHEN right_key < OLD.right_key
 THEN `level` - 1 
 ELSE `level`
 END,
 right_key = CASE WHEN right_key < OLD.right_key
 THEN right_key - 1 
 ELSE right_key - 2
 END
 WHERE (right_key > OLD.right_key OR
 (left_key > OLD.left_key AND right_key < OLD.right_key)) AND
 tree = OLD.tree;
END;</source>{{Hider|end}}

== Добавление триггера ==

Для того чтобы понять работу триггера рассмотрим пример.
 Задание
 Необходимо при добавлении записи в табл user, пароль преобразовывать в хеш md5(), также имя и отчество преобразовывать в инициалы.

* '''Решение'''
<source lang="sql">	 DELIMITER //
	 CREATE TRIGGER `test_user_pass` BEFORE INSERT ON `test`.`user`
	     FOR EACH ROW
	     BEGIN
	      SET NEW.name = LEFT(NEW.name,1);
	      SET NEW.otch = LEFT(NEW.otch,1);
	      SET NEW.pass = md5(NEW.pass);
	     END//
 DELIMITER ;</source>


Теперь вставляем туда запись
<source lang="sql">INSERT  INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';</source>


Что теперь в таблице
	mysql> <source lang="sql">SELECT * FROM `user`;</source>
<source lang="text">	+----+-------------+------+------+----------------------------------+--------+
	| id | fam         | name | otch | pass                             | login  |
	+----+-------------+------+------+----------------------------------+--------+
	|  1 | Нагайченко  | M    | В    | 5f4dcc3b5aa765d61d8327deb882cf99 | maxnag |
	+----+-------------+------+------+----------------------------------+--------+
	 
	1 row in set (0.00 sec)</source>


Как видно всего несколькими строчками можно убрать целые методы, которые мы использовали при регистрации нового пользователя. Теперь еще надо создать триггер на UPDATE таблицы, с таким же телом, чтобы пользователь не смог записать полное имя, отчество и пароль не в МД5();

Создание триггера на BEFORE UPDATE
<source lang="sql">	 DELIMITER //
	 CREATE TRIGGER `test_user_pass2` BEFORE UPDATE ON `test`.`user`
	    FOR EACH ROW
	     BEGIN
	      SET NEW.name = LEFT(NEW.name,1);
	      SET NEW.otch = LEFT(NEW.otch,1);
	      SET NEW.pass = md5(NEW.pass);
	     END//
	 DELIMITER ;
</source>

Обновление записи
<source lang="sql">UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1;</source>


Итог1	
<source lang="sql">SELECT * FROM `user`;</source>
<source lang="text">
2	+----+-------------+------+------+----------------------------------+--------+
3	| id | fam         | name | otch | pass                             | login  |
4	+----+-------------+------+------+----------------------------------+--------+
5	|  1 | Иванов      | И    | И    | e242f36f4f95f12966da8fa2efd59992 | ivan   |
6	+----+-------------+------+------+----------------------------------+--------+
7	 
8	1 row in set (0.00 sec)

</source>
Могут возникнуть вопросы, что такое '''NEW''' в теле триггера,
*'''NEW''' — для доступа к новым записям
*'''OLD''' — для доступа к старым записям
{{ЖЛампа|24px}} Например, если я обновил фамилию, то новое значение мне доступно через NEW.fam, а к старому OLD.fam
=== Список триггеров ===
Как получить список всех триггеров существующих в текущей базе данных? 
<source lang="sql">SHOW TRIGGERS;</source>
=== Как удалить триггер из текущей базы данных? === 
<source lang="sql">DROP TRIGGER `name`;</source>
{{V|24px}}После удаления таблицы, к которой привязан триггер, не стоит пытаться удалить его (триггер) — MySQL сделает это автоматически.

== Транзакции в MySQL ==

Рассмотрим механизм транзакций. Для начала приведем один простой пример.
<source lang="sql">UPDATE user_account SET allsum=allsum + 1000 WHERE id='1';
UPDATE user_account SET allsum=allsum - 1000 WHERE id='2';</source>

Это перевод денег с лицевого счета клиента с номером 2 на лицевой счет клиента с номером 1. А теперь представим что первый запрос удачно выполнился, а вот второй по каким либо причинам (ошибка базы данных, ошибка на сервере и т.д.) нет. Таким образом мы получаем ситуацию которая грозит реальными денежными потерями.

Для того что избежать этого нужно чтобы оба запроса выполнялись как одно целое. И если возникла ошибка в одном запросе не выполнились бы остальные. Для этого и был придуман механизм транзакций.

Оператор открывающий транзакцию в MySQL: 
* '''"START TRANSACTION;"'''
После правильного выполнения всех запросов транзакцию можно либо завершить внеся все изменения в базу данных - 
*'''"COMMIT;"'''
либо откатить вернув все в начальное состояние - 
* '''"ROLLBACK".'''

Если конкретно рассматривать случай с базой MySQL то тут есть несколько подводных камней. 
# В MySQL существует несколько типов таблиц. Это '''ISAM, HEAP, MyISAM, InnoDB, BDB''' и т.д. Транзакционный механизм поддерживают только {{Зел|'''InnoDB и BDB'''}}. Поэтому все таблицы с которыми вы хотите работать через транзакции следует переконвертировать в соответствующий тип.
# По умолчанию MySQL работает в режиме '''autocommit'''. Это означает, что результаты {{Жел|'''''выполнения любого SQL-оператора, изменяющего данные, будут сразу сохраняться'''''}}.

Режим '''autocommit''' можно отключить командой '''SET AUTOCOMMIT=0'''. При отключенном режиме autocommit каждую транзакцию надо явно завершать операторами '''COMMIT / ROLLBACK'''.

Таким образом для того чтобы реализовать однократную транзакцию решающую поставленную в начале статьи проблему нам необходимо выполнить следующий код:

<source lang="sql">START TRANSACTION;
 UPDATE user_account SET allsum=allsum + 1000 WHERE id='1';
 UPDATE user_account SET allsum=allsum - 1000 WHERE id='2';
 COMMIT;</source>