Trigger jest nazwanym obiektem bazodanowym, przypisanym do tabeli, który aktywuje się przy pewnych zdarzeniach tej tabeli. Tymi zdarzeniami mogą być:
- wstawienie nowego wiersza (INSERT)
- zmiana wartości w wierszu (UPDATE)
- usunięcie wiersza (DELETE)
Jak stworzyć trigger?
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON tbl_name FOR EACH ROW
[BEGIN]
trigger_body
[END]
Co powinniśmy wiedzieć o trigger-ach?
- nazwy trigger-ów są unikalne
- w tym samym momencie może istnieć tylko jeden trigger na tej samej tabeli i akcji
- do wierszy updatowanych lub usuwanych możemy się odwoływać przez alias OLD.column_name. Dla insertów ten alias nie istnieje
- do nowego wiersza odwołujemy się przez alias NEW
- wartość dla kolumny AUTO_INCREMENT w aliasie NEW jest 0. Wywołanie sekwencji następuje gdy wiersz faktycznie zostanie wstawiony do tabeli
- trigger-y nie są aktywowane przez zmiany w widokach lub przez trigger-y. Nie są także aktywowane przez klucze obce (FOREIGN KEY)
- w ciele trigger-a możemy deklarować zmienne i error handler-y
- trigger-y są przechowywane w plikach z rozszerzeniem .TRG
- trigger-y mogą wywoływać procedury
- trigger-y nie mogą zwracać wartości. Aby 'wyjść' z trigger-a można użyć polecenia LEAVE
- w ciele trigger-a jest możliwe odwołanie się do innej tabeli. Nie jest możliwe modyfikowanie tabeli, która jest właśnie używana przez wywołanie funkcji lub trigger-a
- w zależności od ustawionej replikacji, zmiany spowodowane przez trigger mogą być w różny sposób widziane na slave. Przy replikacji typu:
- statement - trigger, który jest wywołany na masterze, jest także wykonywany na slave przez wywołanie zapytania
- row - zmiany spowodowane przez wywołanie trigger-a są replikowane na slave
- aby zmodyfikować istniejący trigger, trzeba go usunąć i stworzyć na nowo
- nie jest możliwe tworzenie trigger-ów na bazie 'mysql'
- click_logs - przechowuje wpisy na temat kliknięć jakiś stron, które są identyfikowane przez ID (kolumna site_id). Kolumna timestamp mówi o czasie kliknięcia, a ip_address trzyma informację o adresie IP w formie liczbowej. Kolumna info to dodatkowe informacje np. o przeglądarce klienta
- click_stats - trzyma statystyki kliknięć per strona (site_id) i dzień (stats_date) w kolumnach click_nr (ilość wszystkich kliknięć) i uniq_click_nr (unikalna ilość kliknięć adresów IP)
- addresses - przechowuje unikalne adresy ip (w formie liczbowej) dla każdej strony i dnia - dla potrzeb kolumny uniq_click_nr w tabeli opisanej powyżej
DELIMITER $$
CREATE TRIGGER `click_logs_after_insert` AFTER INSERT ON click_logs FOR EACH ROW
BEGIN
DECLARE stats_exist INTEGER DEFAULT 0;
-- Sprawdzam czy istnieją już jakieś statystyki dla tej strony i tego dnia
SELECT COUNT(*) INTO stats_exist FROM click_stats WHERE site_id = NEW.site_id AND stats_date = date(NEW.timestamp);
IF stats_exist > 0 THEN
-- statystyki już istnieja, dlatego tylko je updatujemy
update click_stats set click_nr = click_nr + 1 WHERE site_id = NEW.site_id AND stats_date = date(NEW.timestamp);
ELSE
-- w tym przypadku statystyki nie istnieją dlatego musi stworzyć pierwszy wiersz
INSERT INTO click_stats (site_id, stats_date, click_nr, uniq_click_nr) VALUES (NEW.site_id, date(NEW.timestamp), 1, 1);
END IF;
END$$
DELIMITER ;
CREATE TRIGGER `click_logs_after_DEL` AFTER DELETE ON click_logs FOR EACH ROW
-- po usunięciu wiersza, zmieniamy także statystyki aby mieć konsystentne dane
UPDATE click_stats SET click_nr = click_nr - 1 WHERE site_id = OLD.site_id AND stats_date = date(OLD.timestamp);
CREATE TRIGGER `click_logs_before_insert` BEFORE INSERT ON click_logs FOR EACH ROW
SET NEW.info = NEW.ip_address;
Listę trigger-ów naszej bazy danych możecie znaleźć pod poleceniem SHOW TRIGGERS;
Przetestujmy działanie tych trigger-ów:
mysql (root@test_triggers)> select * from click_logs;
Empty set (0.00 sec)
mysql (root@test_triggers)> select * from click_stats;
Empty set (0.00 sec)
mysql (root@test_triggers)> select * from addresses;
Empty set (0.00 sec)
mysql (root@test_triggers)> insert into click_logs (site_id, timestamp, ip_address, info) values (1, now(), INET_ATON('127.0.0.1'), 'test');
Query OK, 1 row affected (0.07 sec)
mysql (root@test_triggers)> select * from click_stats;
+----+---------+------------+----------+---------------+
| id | site_id | stats_date | click_nr | uniq_click_nr |
+----+---------+------------+----------+---------------+
| 1 | 1 | 2014-02-04 | 1 | 1 |
+----+---------+------------+----------+---------------+
1 row in set (0.00 sec)
mysql (root@test_triggers)> select * from click_logs;
+----+---------+---------------------+------------+------------+
| id | site_id | timestamp | ip_address | info |
+----+---------+---------------------+------------+------------+
| 1 | 1 | 2014-02-04 18:25:32 | 2130706433 | 2130706433 |
+----+---------+---------------------+------------+------------+
1 row in set (0.00 sec)
mysql (root@test_triggers)> select * from addresses;
Empty set (0.00 sec)
mysql (root@test_triggers)> insert into click_logs (site_id, timestamp, ip_address, info) values (1, now(), INET_ATON('127.20.0.1'), 'test');
Query OK, 1 row affected (0.04 sec)
mysql (root@test_triggers)> select * from click_logs;
+----+---------+---------------------+------------+------------+
| id | site_id | timestamp | ip_address | info |
+----+---------+---------------------+------------+------------+
| 1 | 1 | 2014-02-04 18:25:32 | 2130706433 | 2130706433 |
| 2 | 1 | 2014-02-04 18:26:23 | 2132017153 | 2132017153 |
+----+---------+---------------------+------------+------------+
2 rows in set (0.00 sec)
mysql (root@test_triggers)> select * from click_stats;
+----+---------+------------+----------+---------------+
| id | site_id | stats_date | click_nr | uniq_click_nr |
+----+---------+------------+----------+---------------+
| 1 | 1 | 2014-02-04 | 2 | 1 |
+----+---------+------------+----------+---------------+
1 row in set (0.00 sec)
mysql (root@test_triggers)> insert into click_logs (site_id, timestamp, ip_address, info) values (2, now(), INET_ATON('127.20.0.1'), 'test');
Query OK, 1 row affected (0.08 sec)
mysql (root@test_triggers)> select * from click_logs;
+----+---------+---------------------+------------+------------+
| id | site_id | timestamp | ip_address | info |
+----+---------+---------------------+------------+------------+
| 1 | 1 | 2014-02-04 18:25:32 | 2130706433 | 2130706433 |
| 2 | 1 | 2014-02-04 18:26:23 | 2132017153 | 2132017153 |
| 3 | 2 | 2014-02-04 18:26:45 | 2132017153 | 2132017153 |
+----+---------+---------------------+------------+------------+
3 rows in set (0.00 sec)
mysql (root@test_triggers)> select * from click_stats;
+----+---------+------------+----------+---------------+
| id | site_id | stats_date | click_nr | uniq_click_nr |
+----+---------+------------+----------+---------------+
| 1 | 1 | 2014-02-04 | 2 | 1 |
| 2 | 2 | 2014-02-04 | 1 | 1 |
+----+---------+------------+----------+---------------+
2 rows in set (0.00 sec)
mysql (root@test_triggers)> -- usuwanie
mysql (root@test_triggers)> delete from click_logs where id = 2;
Query OK, 1 row affected (0.03 sec)
mysql (root@test_triggers)> select * from click_logs;
+----+---------+---------------------+------------+------------+
| id | site_id | timestamp | ip_address | info |
+----+---------+---------------------+------------+------------+
| 1 | 1 | 2014-02-04 18:25:32 | 2130706433 | 2130706433 |
| 3 | 2 | 2014-02-04 18:26:45 | 2132017153 | 2132017153 |
+----+---------+---------------------+------------+------------+
2 rows in set (0.00 sec)
mysql (root@test_triggers)> select * from click_stats;
+----+---------+------------+----------+---------------+
| id | site_id | stats_date | click_nr | uniq_click_nr |
+----+---------+------------+----------+---------------+
| 1 | 1 | 2014-02-04 | 1 | 1 |
| 2 | 2 | 2014-02-04 | 1 | 1 |
+----+---------+------------+----------+---------------+
2 rows in set (0.00 sec)
Zmieńmy teraz trigger tak aby uwzględniał także statystykę unikalną. Aby to zrobić, musimy najpierw usunąć stary trigger.
mysql (root@test_triggers)> Drop trigger click_logs_after_insert; Query OK, 0 rows affected (0.04 sec)
Tworzymy nowy trigger:
CREATE TRIGGER `click_logs_after_insert` AFTER INSERT ON click_logs FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one BEGIN DECLARE stats_exist INTEGER DEFAULT 0; DECLARE address_exist_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET address_exist_error = 1; SELECT COUNT(*) INTO stats_exist FROM click_stats WHERE site_id = NEW.site_id AND stats_date = date(NEW.timestamp); INSERT INTO addresses (ip_address, site_id, stats_date) values (NEW.ip_address, NEW.site_id, date(NEW.timestamp)); IF stats_exist > 0 THEN IF address_exist_error = 1 THEN update click_stats set click_nr = click_nr + 1 WHERE site_id = NEW.site_id AND stats_date = date(NEW.timestamp); else update click_stats set click_nr = click_nr + 1, uniq_click_nr = uniq_click_nr + 1 WHERE site_id = NEW.site_id AND stats_date = date(NEW.timestamp); end if; ELSE INSERT INTO click_stats (site_id, stats_date, click_nr, uniq_click_nr) VALUES (NEW.site_id, date(NEW.timestamp), 1, 1); END IF; END
Tym razem będziemy mieli wpisy w tabeli addresses:
mysql (root@test_triggers)> truncate table addresses; truncate table click_logs; truncate table click_stats;
Query OK, 0 rows affected (0.16 sec)
Query OK, 0 rows affected (0.12 sec)
Query OK, 0 rows affected (0.15 sec)
mysql (root@test_triggers)> insert into click_logs (site_id, timestamp, ip_address, info) values (2, now(), INET_ATON('127.20.0.1'), 'test');
Query OK, 1 row affected (0.04 sec)
mysql (root@test_triggers)> select * from click_logs;
+----+---------+---------------------+------------+------------+
| id | site_id | timestamp | ip_address | info |
+----+---------+---------------------+------------+------------+
| 1 | 2 | 2014-02-04 19:00:29 | 2132017153 | 2132017153 |
+----+---------+---------------------+------------+------------+
1 row in set (0.00 sec)
mysql (root@test_triggers)> select * from click_stats;
+----+---------+------------+----------+---------------+
| id | site_id | stats_date | click_nr | uniq_click_nr |
+----+---------+------------+----------+---------------+
| 1 | 2 | 2014-02-04 | 1 | 1 |
+----+---------+------------+----------+---------------+
1 row in set (0.00 sec)
mysql (root@test_triggers)> select * from addresses;
+------------+---------+------------+
| ip_address | site_id | stats_date |
+------------+---------+------------+
| 2132017153 | 2 | 2014-02-04 |
+------------+---------+------------+
1 row in set (0.00 sec)
mysql (root@test_triggers)> insert into click_logs (site_id, timestamp, ip_address, info) values (2, now(), INET_ATON('127.20.0.1'), 'test');
Query OK, 1 row affected (0.04 sec)
mysql (root@test_triggers)> select * from click_logs;
+----+---------+---------------------+------------+------------+
| id | site_id | timestamp | ip_address | info |
+----+---------+---------------------+------------+------------+
| 1 | 2 | 2014-02-04 19:00:29 | 2132017153 | 2132017153 |
| 2 | 2 | 2014-02-04 19:03:16 | 2132017153 | 2132017153 |
+----+---------+---------------------+------------+------------+
2 rows in set (0.00 sec)
mysql (root@test_triggers)> select * from click_stats;
+----+---------+------------+----------+---------------+
| id | site_id | stats_date | click_nr | uniq_click_nr |
+----+---------+------------+----------+---------------+
| 1 | 2 | 2014-02-04 | 2 | 1 |
+----+---------+------------+----------+---------------+
1 row in set (0.00 sec)
mysql (root@test_triggers)> select * from addresses;
+------------+---------+------------+
| ip_address | site_id | stats_date |
+------------+---------+------------+
| 2132017153 | 2 | 2014-02-04 |
+------------+---------+------------+
1 row in set (0.00 sec)
Dla statystyk unikalnych możemy mieć problem przy usuwaniu danych tak jak chcieliśmy to zrobić dla zwykłych kliknięć. Nie będę jednak się tym przejmować, bo jest to tylko przykład pokazujący jak tworzyć trigger-y.
Pamiętajmy, że trigger-y mogą spowolnić wykonywanie tylko podstawowych operacji (INSERT, UPDATE, DELETE) albo spowodować bałagan. Dlatego twórzmy je z rozwagą.
