1: triggers для mysql
2: Что есть триггер? Триггер - это хранимая процедура особого типа, исполнение которой обусловлено наступлением определенного события (event). Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Все производимые ими модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции. Все триггеры в Вашей базе можно рассматривать, как event-manager связанный с событиями изменения данных (или их количества) в одних таблицах и вызывающий изменение данных в других.
3: Events BEFORE – до начала AFTER – после выполнения Эти события можно привязывать к операциям: INSERT UPDATE DELETE Т. е. вы можете определить действия: BEFORE INSERT или AFTER INSERT BEFORE UPDATE или AFTER UPDATE BEFORE DELETE или AFTER DELETE
4: Events BEFORE – используется для «перехвата» входящего запроса, и изменения данных в самом запросе или/и упреждающего изменения данных в другой таблице базы. AFTER – используется только для принятия решения об изменении данных в какой либо из таблиц базы на основании уже «случившегося» события.
5: Trigger «слушатель» Как уже понятно, триггеры привязаны к таблицам, в которых «слушают события». TRIGGER firsttrigger AFTER INSERT ON user FOR EACH ROW BEGIN …. обычный SQL ;) …. END Триггер с именем firsttrigger «слушает» события INSERT в таблице user и, после выполнения операции, для каждой изменённой строки выполняет некое SQL – выражение.
6: «Болванка» для триггера Пример запроса на создание триггера на событие BEFORE UPDATE для таблицы user: DELIMITER $$ DROP TRIGGER firsttrigger$$ CREATE TRIGGER firsttrigger BEFORE UPDATE ON user FOR EACH ROW BEGIN …. обычный SQL ;) …. END;$$ DELIMITER ;
7: Боевой пример Пусть у нас будут 2 таблицы: User userid (идентификатор юзера в проекте) status (0 – для не подтверждённых юзеров, 1 – для подтверждённых) userid - primary key, autoincrement. - - - - - Confirm userid (идентификатор юзера) activkey (идентификатор действия) userid - foreign key
8: Боевой пример Допустим, речь идёт о стандартной регистрации в проекте. Юзер регистрируется: в таблице user создаётся запись с данными юзера и status0 INSERT INTO user(status, …) VALUES (0, …. ) В этот момент user получает свой userid. Пусть для примера, userid будет 123456. в таблице confirm создаётся запись с активационным ключом для подтвреждения персоны INSERT INTO confirm(userid, activkey) VALUES (123456, somevalue) И нашему юзеру на почту падает письмо со ссылкой типа: Подтвердить регистрацию
9: Боевой пример Юзер переходит по ссылке, мы вынуждены сделать запросы: 1) SELECT activkey FROM confirm WHERE userid123456; проверка: совпадает ли activkey в базе и из ссылки 2) UPDATE user SET status1 WHERE userid123456; обновление: выставляем статус «подтверждён» для персоны 3) DELETE FROM confirm WHERE userid123456; удалем запись из базы confirm, т. к. человек себя уже подтвердил 4) SELECT FROM user WHERE userid123456; забираем данные персоны. Т. к. после подтверждения, нам нужно отрисовать страницу, где мы поздравляем юзера с успешной регистрацией, и (может) обращаемся по имени и пр. Конечно, можно сделать и проще, но суть останется прежней.
10: Боевой пример DELIMITER $$ DROP TRIGGER confirm$$ CREATE TRIGGER confirm AFTER DELETE ON confirm FOR EACH ROW BEGIN UPDATE user SET status1 WHERE useridOLD. userid; END;$$ DELIMITER ; Теперь, мы можем поступить так (вместо варианта на предыдущей странице): DELETE FROM confirm WHERE userid123456 AND activkeysomevalue; SELECT FROM user WHERE userid123456; И просто проверить user. status, если он равен 1, то юзер себя уже подтвердил.
11: OLD и NEW Как было указано в предыдущем примере, в триггерах используются две парадигмы OLD и NEW. OLD – значение в таблице до события. NEW – значение в таблице после события. Например, для события DELETE существует только OLD. Для события INSERT только NEW. А для UPDATE и то и другое. Всем этим арсеналом пользуются для весьма гибкого переопределения значений. Т. к. триггеры в «теле» содержат sql, который поддерживает условия IF THEN ELSE (см доки по mysql), то вполне допустимо использование: IF (NEW. status 1 AND NEW. status ! OLD. status) THEN SET NEW. somefieldsomevalue; END IF;
12: Несколько примеров DELIMITER $$ DROP TRIGGER newscounter$$ CREATE TRIGGER newscounter AFTER INSERT ON newscomments FOR EACH ROW BEGIN UPDATE news SET comments count commentscount 1 WHERE newsid NEW. newsid END;$$ DELIMITER ; Счётчик кол-ва комментариев хранится в таблице news (с самими новостями), а комментарии - в таблице newscomments. Триггер выполнит инкремент счётчика, при добавлении нового комментария. NEW. newsid относится к новой записи в таблице newscomments.
13: Несколько примеров DELIMITER $$ DROP TRIGGER categoryflagchanger$$ CREATE TRIGGER categoryflagchanger BEFORE INSERT ON categorynews FOR EACH ROW BEGIN IF NEW. categoryid 12 THEN SET NEW. someflag 1; ELSE SET NEW. someflag 0; END IF; END;$$ DELIMITER ; Здесь видно, что для категории 12 выставляется флаг «someflag» вне зависимости от того значения, что пришло в запросе.
14: Заключение Эта презентация не раскрывает всей силы использования триггеров в проекте, но хотя бы избавит от «страха» использовать этот инструмент в работе. Удачи.
