Triggery i replikacja na przykładzie Mariadb 10.2

Trigger jest programem powiązanym z tabelą w bazie danych, który jest wykorzystywany do automatycznego wykonywania niektórych działań, gdy zdarzenie INSERT, DELETE lub UPDATE jest wykonywane na tabeli. Trigger ustawia się aby wykonywał akcję przed lub po zdarzeniu, z którym jest powiązany.

Definicja triggeru:

CREATE TRIGGER trigger_name {BEFORE | AFTER } {INSERT | DELETE | UPDATE}
ON table_name
FOR EACH ROW trigger_stmt

Typy zapytań wywołujące triggery (o czym powinniśmy pamiętać):

• INSERT
• UPDATE
• DELETE
• LOAD DATA INFILE = INSERT trigger
• LOAD XML = INSERT trigger
• REPLACE:
• BEFORE INSERT
• BEFORE DELETE (tylko gdy wiersz zostanie usunięty)
• AFTER DELETE (tylko jeśli wiersz zostanie usunięty)
• AFTER INSERT
• INSERT ... ON DUPLICATE KEY UPDATE kiedy wiersz już istnieje, wywołuje następujące triggery: 
• BEFORE INSERT, 
• BEFORE UPDATE, 
• AFTER UPDATE

Także ważne informacje:

• Polecenie TRUCATE TABLE (DELETE wszystkich wierszy) nie wywołuje żadnego triggera.
• Triggery są wywowływane w tej same transakcji co wywołujące je zapytanie, dla enginów transakcyjnych (np. InnoDB). Dlatego zapytanie wywołujące będzie cofnięte jeśli trigger AFTER wywoła błąd.
• Dla engingów nietransakcyjnych, jeśli BEFORE trigger wywoła błąd, to zapytanie jego wywołujące się nie wykona, lecz gdy trigger jest AFTER, to zapytanie wywołujące już się wykona.

Przykład tabeli i triggera:

CREATE TABLE `AppLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `conId` int(10) NOT NULL,
  `appId` varchar(50) DEFAULT NULL,
  `clientStatus` varchar(250) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER ;; 

CREATE DEFINER=`root`@`%` TRIGGER log_app_status_trigger
  AFTER INSERT ON AppTable FOR EACH ROW BEGIN
    INSERT INTO AppLog (
  conId,
  appId,
  clientStatus
 ) VALUES (
  NEW.con,
  NEW.apptId,
  NEW.appStatus

 );

END;;

DELIMITER ; 

Po uruchomieniu triggera wypełniane są dwa pseudorekordy: OLD i NEW: wartości przypisane do nich różnią się w zależności od typu zdarzenia. W przypadku instrukcji INSERT, ponieważ wiersz jest nowy, pseudorekord OLD nie będzie zawierał żadnych wartości, a NEW zawiera wartości nowego wiersza, który powinien zostać wstawiony. W przypadku instrukcji DELETE stanie się odwrotnie: OLD będzie zawierać stare wartości, a NEW będzie puste. Dla instrukcji UPDATE, oba będą wypełnione, ponieważ OLD będzie zawierał stare wartości wiersza, a NEW zawiera nowe.

Zarządzanie triggerami

Lista wszystkich triggery:

SHOW triggers;

Definicja wybranego triggera:

SHOW CREATE TRIGGER log_app_status_trigger; 

Usunięcie triggera (Aby zmienić już istniejącego triggera, trzeba go usunąć i stworzyć na nowo):

DELETE TRIGGER log_app_status_trigger;

Restrykcje dodatyczące triggerów dla MariaDB 10.2:

• aż do wersji 10.2 każda tabela mogła mieć tylko jeden trigger z tym samym typem i czasem zdarzenia, np. tabela może mieć tylko jeden trigger BEFORE INSERT
• triggery są zawsze wykonywane dla każdego wiersza, dlatego opcja FOR EACH STATEMENT nie jest wspierana
• nie można zdefiniować triggera dla baz mysql, information_schema czy performance_schema
• triggery nie są aktywowane przez akcje kluczy obcych (foreign key)
• wszystkie restrycje dotyczące funkcji i procedur bazodanowych tyczą się też triggerów
• jeśli trigger jest załadowany do cache, nie jest automatycznie przeładowywany, kiedy metadata tabeli są zmieniane. W takim przypadku trigger może operować na starych metadatach
• domyślnie, akcje triggerów są replikowane automatycznie. Przy użyciu replikacji typu STATEMENT, binary logi zawierają zapytania, które są odpalane na masterze i slave, niezależnie. Przy replikacji typu ROW, binary logi zawierają zmiany wierszy wykonanych przez zapytanie, jak i triggery je wywołujące (Takie zmiany w bin-logach są flagowane jako wywołane przez trigger.). Slave nie musi uruchamiać triggerów. Od wersji MariaDB 10.1.1 jest możliwe uruchamianie triggera tylko na slave.

Jak uruchomić triggera na slave, gdy wiersze są zmieniane i replikowane z mastera? Służy do tego zmienna globalna:

set global slave_run_triggers_for_rbr = YES

Dokumentacja na temat slave_run_triggers_for_rbr znajduje się tutaj.

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