Данная статья посвящена триггерам в MySQL и основана на официальной документации.
Триггер это именованный объект БД, ассоциированный с таблицей и который активируется при insert / update / delete. Причем активация может быть до события и после (before, after). Можно использовать триггеры для проверки значений, вставляемых в таблицу или проводить расчеты.
То есть мы можем создавать триггеры для следующих событий
|
1 2 3 4 5 |
BEFORE INSERT и AFTER INSERT… BEFORE UPDATE и AFTER UPDATE BEFORE DELETE и AFTER DELETE… |
Пример триггера из документации
|
1 2 3 4 5 |
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.06 sec) |
Этот код выполнен в консоли о чем свидетельствует выражение “mysql>”. Что собственно в этом коде происходит? А все очень просто – создается таблица, а в триггере объявляется аккумулирующее суммирование.
Символ @ перед переменной означает глобальную переменную. Её сервер mysql будет помнить после выхода из триггера и её же можно будет потом вызвать, например так
|
1 |
select @sum; |
NEW означает, что мы берем новое значение, а не старое (для старого используется OLD, но в случае инструкции INSERT его и нет, об этом поговорим ниже)
FOR EACH ROW это что-то вроде цикла, то есть то, что идет до следующей точки с запятой будет повторяться для каждого ряда таблицы, к которой прикреплен триггер.
Далее, попробуем вставить данные и посмотреть что произошло.
|
1 2 3 4 5 6 7 8 |
mysql> SET @sum = 0; mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> SELECT @sum AS 'Total amount inserted'; +-----------------------+ | Total amount inserted | +-----------------------+ | 1852.48 | +-----------------------+ |
Сервер произвел расчет примерно таким образом
|
1 |
14.98 + 1937.50 - 100, или 1852.48. |
Чтобы удалить триггер можно использовать следующую инструкцию
|
1 |
mysql> DROP TRIGGER test.ins_sum; |
При удалении таблицы все триггеры, связанные с таблицей удаляются.
OLD and NEW
NEW эти данные только вошли.
OLD данные уже обработаны. Read-only.
Разберемся более внимательно с тем, что такое OLD and NEW. Эти инструкции позволяют получать доступ к колонкам с которыми работает триггер. Эти инструкции относятся исключительно к MySQL. Они не чувствительны к регистру.
В INSERT триггере, только NEW.col_name может быть использовано. Это и понятно, там OLD.col_name ещё и нет.
В DELETE триггере только OLD.col_name может быть использовано.
В UPDATE триггере можно использовать и то и другое.
Более детально можно посмотреть в документации
Следующий пример
Следующий пример позволяет создавать триггеры из нескольких инструкций. Все что нужно это заключить их в BEGIN… …END, а также объявить другой delimiter на время исполнения триггера.
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> delimiter // mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW.amount < 0 THEN -> SET NEW.amount = 0; -> ELSEIF NEW.amount > 100 THEN -> SET NEW.amount = 100; -> END IF; -> END;// mysql> delimiter ; |
Внутри триггера также можно вызвать хранимую процедуру при помощи метода CALL, но не возвращающую множество.
Также внутри триггера нельзя использовать транзакции.
Триггеры могут иметь прямые ссылки на таблицы, например таким образом
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT); CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 ); delimiter | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | delimiter ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0); |
Теперь, допустим, мы произвели вставку данных таким образом
|
1 2 3 4 |
mysql> INSERT INTO test1 VALUES -> (1), (3), (1), (7), (1), (8), (4), (4); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 |
Результаты будут следующими
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
mysql> SELECT * FROM test1; +------+ | a1 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | mysql> SELECT * FROM test1; +------+ | a1 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | mysql> SELECT * FROM test3; +----+ | a3 | +----+ | 2 | | 5 | | 6 | | 9 | | 10 | +----+ 5 rows in set (0.00 sec) mysql> SELECT * FROM test4; +----+------+ | a4 | b4 | +----+------+ | 1 | 3 | | 2 | 0 | | 3 | 1 | | 4 | 2 | | 5 | 0 | | 6 | 0 | | 7 | 1 | | 8 | 1 | | 9 | 0 | | 10 | 0 | +----+------+ 10 rows in set (0.00 sec) |
Таким образом, мы рассмотрели основные возможности триггеров.