СУБД/Практические занятия №2(2 семестр)

Содержание

1 Триггеры (MySQL)
2 Что необходимо для работы (ограничения, не все)
3 Добавление триггера
- 3.1 Список триггеров
- 3.2 Как удалить триггер из текущей базы данных?
4 Транзакции в MySQL

Триггеры (MySQL)

https://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

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 }

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

Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы. Получается, что триггер может быть в 6 состояниях.

	INSERT (BEFORE | AFTER)
	UPDATE (BEFORE | AFTER)
	DELETE (BEFORE | AFTER)

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

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

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;

В противном случае Вы получите такую ошибку: 
#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

В-четвёртых, значения надо менять до обновления. В конечном счёте правильный вариант:

CREATE TRIGGER `artists_litera_autoupdate` BEFORE UPDATE ON `cms_artists`
 FOR EACH ROW BEGIN
    SET NEW.`litera`= UPPER(SUBSTRING(NEW.`name`, 1, 1));
 END;

В-пятых, при очищении таблицы (TRUNCATE) триггер, повешенный на событие DELETE, исполняться не будет.
В MySQL нет проблемы с рекурсивными триггерами! Разработчики MySQL просто лочат изменяемую таблицу даже на уровне триггера.

DELIMITER $$
CREATE DEFINER=`root`@`%` TRIGGER mytrigger
BEFORE INSERT ON mytable
FOR each ROW
BEGIN
	UPDATE mytable
	   SET a = 123
	WHERE (id=1);
END$$

Выполняться не будет

MySQL диалект триггеров несколько отличается от диалекта PostgreSQL:
- Не обязательно объявлять переменные в начале процедуры;
- Прервать выполнение триггера нельзя, он должен отработать полностью;
- Как было сказано выше, рекурсии мы не боимся, поэтому лишние проверки и дополнительные поля нам не потребуются.
Поэтому несколько изменяется логика:
- Переменные появляются по ходу кода;
- Вместо возвратов из триггера, оборачиваем код условиями;
- Все изменения, касающиеся структуры дерева, применяем к вспомогательной объединенной таблице;
В MySQL имеет значение порядок перечисления полей в запросе UPDATE, так как поля изменяемые во время запроса в самом же запросе меняют значение на новое, поэтому, если мы дальше по запросу используем эти поля в условиях, результат будет неадекватным.

Пример 1 (Создание записи)

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;

Пример 2 (Изменение записи)

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;

Пример 3 (Удаление записи)

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;

Пример 4 (Удаление узла со смещением дочерних узлов на уровень в вверх)

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;

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

Для того чтобы понять работу триггера рассмотрим пример.

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

Решение

	 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 ;

Теперь вставляем туда запись

INSERT  INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';

Что теперь в таблице mysql>

SELECT * FROM `user`;

	+----+-------------+------+------+----------------------------------+--------+
	| id | fam         | name | otch | pass                             | login  |
	+----+-------------+------+------+----------------------------------+--------+
	|  1 | Нагайченко  | M    | В    | 5f4dcc3b5aa765d61d8327deb882cf99 | maxnag |
	+----+-------------+------+------+----------------------------------+--------+
 
	1 row in set (0.00 sec)

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

Создание триггера на BEFORE UPDATE

	 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 ;

Обновление записи

UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1;

Итог1

SELECT * FROM `user`;

2	+----+-------------+------+------+----------------------------------+--------+
3	| id | fam         | name | otch | pass                             | login  |
4	+----+-------------+------+------+----------------------------------+--------+
5	|  1 | Иванов      | И    | И    | e242f36f4f95f12966da8fa2efd59992 | ivan   |
6	+----+-------------+------+------+----------------------------------+--------+
7	 
8	1 row in set (0.00 sec)

Могут возникнуть вопросы, что такое NEW в теле триггера,

NEW — для доступа к новым записям
OLD — для доступа к старым записям

Например, если я обновил фамилию, то новое значение мне доступно через NEW.fam, а к старому OLD.fam

Список триггеров

Как получить список всех триггеров существующих в текущей базе данных?

SHOW TRIGGERS;

Как удалить триггер из текущей базы данных?

DROP TRIGGER `name`;

После удаления таблицы, к которой привязан триггер, не стоит пытаться удалить его (триггер) — MySQL сделает это автоматически.

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

Рассмотрим механизм транзакций. Для начала приведем один простой пример.

UPDATE user_account SET allsum=allsum + 1000 WHERE id='1';
UPDATE user_account SET allsum=allsum - 1000 WHERE id='2';

Это перевод денег с лицевого счета клиента с номером 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.

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

START TRANSACTION;
 UPDATE user_account SET allsum=allsum + 1000 WHERE id='1';
 UPDATE user_account SET allsum=allsum - 1000 WHERE id='2';
 COMMIT;