Triggers w MySQL-u

Jakiś czas temu postanowiłam dowiedzieć się co nieco o trigger-ach. O to co dowiedziałam się o nich w MySQL-u w wersji 5.5.

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)

Obiekt jest aktywowany przed (BEFORE) lub po (AFTER) zdarzeniu.

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'

Aby pokazać przykładowe działanie triggerów, przygotowałam małą bazę danych z kilkoma tabelami

Tabele:

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

Stworzyłam na razie 3 triggery dla tabeli click_logs:

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ą.

DB Diary - Elżbieta Dulko

piątek, 7 lutego 2014

Triggers w MySQL-u

Brak komentarzy:

Prześlij komentarz