Большая часть кода, который мы пишем, предназначена для выполнения действий. Будь то для запросов к базе данных, манипулирования файлами, обработки данных и т. Д., Все это необходимо для того, чтобы наши скрипты работали в соответствии с поставленной целью. Но вы когда-нибудь замечали объем кода, который вам приходилось иногда выписывать, чтобы проверить некоторые предыдущие действия?
В одном из моих недавних проектов возникла довольно громоздкая проблема, из-за которой я использовал бесчисленные запросы, чтобы убедиться, что все данные синхронизированы во всех моих таблицах после каждого действия. Это было далеко не элегантно, и то, что должно было быть довольно простым сценарием, превратилось в запутанную страницу запросов. Это было нежизнеспособно с точки зрения обслуживания и было полным кошмаром, когда я хотел обновить часть функциональности страницы. Именно здесь триггеры MySQL вошли в мой проект.
Благодаря тому, что MySQL делает больше работы через триггеры, PHP-сторона моего проекта была значительно упрощена. Итак, цель этой статьи — дать вам некоторое представление о создании и использовании триггеров MySQL, чтобы к концу этого чтения вы могли использовать их в своих собственных проектах.
Кто они такие?
Триггеры были введены в MySQL версии 5.0.2 и являются лишь одной из дополнительных функциональных возможностей MySQL, которые помогают сделать нашу жизнь проще как разработчикам. Они автоматически вызываются до или после выполнения действия (вставка, обновление, удаление) над таблицей.
Вам нужно будет иметь соответствующие привилегии для создания триггеров. До MySQL 5.1.6 вам требовалась привилегия SUPER, но в 5.1.6 она изменилась, и вам понадобилась привилегия TRIGGER. Как правило, никакой план общего хостинга не позволит вам SUPER из-за того, как легко им можно злоупотреблять, поэтому вы можете использовать их только на сервере, на котором у вас больше полномочий, например (виртуальный) выделенный сервер или ваш локальный хост, в зависимости от версия MySQL, которую вы используете.
Вот несколько других заметок, касающихся триггеров:
- Они должны иметь уникальные (без учета регистра) имена в базе данных, в которой они были созданы.
- Только один триггер с одинаковым событием (обновление / вставка / удаление) и временем (до / после) разрешен для каждой таблицы.
- После удаления таблицы триггеры, связанные с ней, также удаляются.
- Вы не можете явно изменить триггер с помощью инструкции
ALTER(в отличие от событий). Вам нужно будет отпустить курок и воссоздать его. - Триггеры запускаются только тогда, когда был выполнен необработанный оператор SQL; Например, удаление отношения внешнего ключа не активирует триггер.
Теперь давайте подробнее рассмотрим базовый синтаксис триггера, приведя его к исходному виду:
СОЗДАТЬ ТРИГГЕР TrigName [ПЕРЕД | ПОСЛЕ] [ВСТАВИТЬ | ОБНОВИТЬ | УДАЛИТЬ] ON tableName ДЛЯ КАЖДОГО РЯДА НАЧАТЬ #action (s) для выполнения КОНЕЦ
При создании триггера вы можете выбрать, будет ли он срабатывать до или после действия; какой из них вы выберете, будет полностью зависеть от ситуации, в которой вы их используете. Если вы хотите изменить входящие данные, поступающие в вашу базу данных, то BEFORE необходим. Однако, если вы хотите выполнить действие из-за предыдущего, тогда следует использовать оператор AFTER . Действие, которое сработает из триггера, может быть INSERT , UPDATE или DELETE потому что это только три оператора, которые приведут к изменению данных в таблице.
Применение триггера к ситуации
Есть несколько ситуаций, когда триггеры могут пригодиться. Одним из общеизвестных применений является поддержание целостности данных в наборе таблиц с помощью инициированного удаления устаревших записей, когда внешний ключ не используется. Их также можно использовать для автоматического увеличения или уменьшения таблицы статистики при новой вставке / удалении, регистрации изменений, внесенных в данные в базе данных, синхронизации таблиц с другими таблицами и т. Д.
В этой статье мы будем использовать их для предварительной обработки некоторых вычислений. Сценарий таков, что у нас есть компания, которая арендует свой зал по цене 30 фунтов стерлингов в час. Они регистрируют имя, время начала и время окончания каждого мероприятия, проведенного там, в таблице events , а затем вычисляют время и сборы, подлежащие revenue таблице revenue .
Имя и время начала events сначала вставляются в таблицу events чтобы предварительно забронировать зал, и только после завершения мероприятия стоимость аренды обновляется в строке. Продолжительность мероприятия должна быть рассчитана (в минутах), где начальное время будет вычтено из конечного времени, а затем плата за аренду будет рассчитана путем умножения общего времени на 0,5 (30 фунтов стерлингов в час — 50 пенсов за минуты).
Мы можем либо выполнить расчеты для продолжительности события и сборов с помощью PHP (выбрав вставленные данные, рассчитав продолжительность и арендную плату, а затем вставив или обновив таблицу доходов) после обновления информации о событии, либо мы можем просто использовать триггер, чтобы автоматизировать этот процесс и отключить PHP.
Давайте настроим две основные таблицы и вставим некоторые фиктивные данные о бронировании в events чтобы начать все сначала.
CREATE TABLE events ( id INTEGER NOT NULL AUTO_INCREMENT, event_name VARCHAR(50) NOT NULL, event_start TIMESTAMP NOT NULL DEFAULT 0, event_end TIMESTAMP NOT NULL DEFAULT 0, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE revenue ( id INTEGER NOT NULL AUTO_INCREMENT, event_id INTEGER NOT NULL, hire_time INTEGER NOT NULL, hire_fees FLOAT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE, UNIQUE (event_id) )ENGINE=INNODB; INSERT INTO events VALUES (NULL, 'Birthday Party', '2012-11-08 14:30:00', 0), (NULL, 'Wedding', '2012-12-02 13:00:00', 0);
Как только мы настроим две таблицы, мы можем перейти к созданию триггера, который мы назовем CostCalc . Триггер устанавливается на срабатывание после обновления таблицы events , которое затем выполнит вычисление, указанное ранее. Затем он вставляет или обновляет (если ранее существующий идентификатор события уже установлен) таблицу revenue .
DELIMITER ^^ CREATE TRIGGER CostCalc AFTER UPDATE ON events FOR EACH ROW BEGIN DECLARE rows BOOL DEFAULT 0; DECLARE time INT DEFAULT 0; SELECT COUNT(id) INTO rows FROM events WHERE id = NEW.id AND (OLD.event_start != NEW.event_start OR OLD.event_end != NEW.event_end) AND NEW.event_end != 0; IF(rows = 1) THEN SET time = TIMESTAMPDIFF(MINUTE, NEW.event_start, NEW.event_end); REPLACE INTO revenue VALUES (NULL, NEW.id, time, time * 0.5); END IF; END ^^
Первое, что нам нужно сделать при создании триггера (аналогично событиям и хранимым подпрограммам), это указать новый разделитель, обозначающий конец триггера. Это делается с помощью ключевого слова DELIMITER , за которым следует пользовательский символ (или символы), и требуется для выполнения триггера в целом, а не для MySQL, просто выполняющего инструкции внутри по отдельности.
Затем мы указываем имя триггера, его время, событие и таблицу, для которой он будет настроен. В этом примере мы синхронизировали триггер для действия AFTER того, как произошла инструкция UPDATE , потому что мы хотим выполнить триггер только после успешного обновления; в противном случае мы дублировали бы предыдущую запись этого события. Далее мы используем составной оператор BEGIN...END для размещения функциональности триггера.
Тело триггера начинается с объявления двух переменных: rows и time . Мы выбираем количество строк в таблице events где идентификатор ссылается на только что измененную строку, и где были изменены (или оба) значения времени event_start и event_end , а также где время окончания события не равно нуль. Это необходимо для того, чтобы выяснить, действительно ли обновленная строка должна что-либо делать с таблицей revenue потому что только с этими изменениями можно рассчитать арендную плату. Как только мы узнаем, что у нас есть возможность рассчитать время и сборы, мы устанавливаем переменную времени равной количеству минут от начала до конца столбцов события. Умножив это число на 0,5, мы также получим стоимость аренды. Поскольку столбец event_id уникален, мы можем иметь только один идентификатор, соответствующий таблице событий; и поэтому мы используем REPLACE для обновления либо ранее существующей строки в таблице новыми данными, либо вставляем новую строку, если она еще не существует.
Внутри операторов MySQL вы также могли заметить, что ключевые слова OLD и NEW используются в вышеприведенных SELECT и REPLACE , а также в выражении для значения time переменной. Когда вы их используете, это будет зависеть от события вашей ситуации, а также от времени срабатывания вашего спускового крючка.
- Ключевое слово
NEWиспользуется для доступа к входящим данным в базу данных. Это доступно только для операторовINSERTиUPDATE. - Ключевое слово
OLDиспользуется для доступа к текущим данным внутри записи до того, как в нее были внесены какие-либо изменения. Это доступно только для операторовUPDATEиDELETE.
Соответствующий скрипт PHP, который будет использоваться для запуска нашего триггера, будет включать в себя класс (называемый EventHandler ) и наш клиентский код вызова. Класс будет подключаться к нашей базе данных MySQL через PDO и будет содержать один метод updateEvent() , который будет вызываться, когда необходимо обновить содержимое события.
<?php class EventHandler { protected $db; public function __construct(PDO $db) { $this->db = $db; $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } public function updateEvent($type, $param, $id) { if (!in_array($type, array('name', 'start', 'end'), TRUE)) { throw new InvalidArgumentException( 'No such column type exists.' ); } $query = $this->db->prepare( "UPDATE events SET event_{$type} = :param WHERE id = :ID" ); $query->bindParam(':param', $param, PDO::PARAM_STR); $query->bindParam(':ID', $id, PDO::PARAM_INT); $query->execute(); if($query->rowCount() !== 1) { throw new InvalidArgumentException( 'No such event ID exists.' ); } } } $dsn = 'mysql:dbname=events'; $dbuser = 'dbuser'; $passwd = 'dbpassword'; $settings = array(PDO::MYSQL_ATTR_FOUND_ROWS => TRUE); $eventHandler = new EventHandler(new PDO($dsn, $dbuser, $passwd, $settings)); // will not cause any inserts or updates to the revenue table $eventHandler->updateEvent('name', 'Auction', 1); $eventHandler->updateEvent('start', '2012-11-10 14:30:00', 1); // causes a new insertion into the revenue table $eventHandler->updateEvent('end', '2012-12-02 20:30:00', 2); // causes an update on the revenue table $eventHandler->updateEvent('end', '2012-12-02 21:30:00', 2);
Мы начнем с создания нашего класса EventHandler , где определено свойство $db для хранения экземпляра класса PDO который устанавливается с помощью метода конструктора. Затем мы переходим к созданию нашего updateEvent() с тремя заданными аргументами. Первый аргумент указывает столбец, который мы хотим обновить в нашей таблице events , и ему разрешено одно из трех значений: имя, начало, конец. Второй аргумент содержит значение для вставки или обновления текущего значения столбца; в то время как третий аргумент содержит идентификатор кортежа для обновления. Убедившись в правильности имени столбца, мы запрашиваем нашу таблицу с помощью параметризованных запросов и, наконец, проверяем, обновляются ли какие-либо строки.
После создания класса мы переходим к его названию. Мы передаем экземпляр объекта PDO через класс EventHandler в качестве параметра. Затем updateEvent() вызывается четыре раза с различными значениями, чтобы показать, как наш триггер будет реагировать на обновления, сделанные в нашей таблице events . Первые два обновления не приведут к тому, что наш триггер вставит или обновит строку, потому что у нас все еще нет необходимой информации для расчета продолжительности события и стоимости арендной платы. Все, что мы сделали, это обновили название события и отложили время его начала на два дня. Следующим двум обновлениям, однако, потребуется функциональность нашего триггера, поскольку первое обновление определяет время окончания, а второе обновление переопределяет время окончания на один час позже, вызывая изменение продолжительности и, следовательно, арендной платы. Именно здесь требуется наш оператор REPLACE , поскольку из-за ограничения, которое мы наложили на таблицу при создании, мы можем иметь только одну запись для каждого идентификатора события.
Заключительные замечания
При эффективном использовании триггеры MySQL могут не только положительно повлиять на производительность вашего сайта, но и избавить вас от написания многочисленных строк PHP для выполнения таких действий. Я надеюсь, что вы найдете их такими же полезными в ваших проектах, как и мои, так что не стесняйтесь, чтобы получить удовольствие от триггера!
Изображение через Fotolia