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.