Czasami, podczas wykonania pewnych operacji na tabelach w bazie danych trzeba dodatkowo zaktualizować inną tabelę i/lub wykonać pewne obliczenia, za każdym razem, gdy np. dodamy nowy rekord. Nie zawsze jest możliwość realizacji powyższego zadania w kodzie aplikacji, dlatego warto zastanowić się nad użyciem triggerów.

Pisząc poniższą definicję posłużyłem się wikipedią oraz dokumentacją MySQL.

Trigger (pl. wyzwalacz) jest to procedura, która jest połączona z konkretną tabelą w bazie danych i wywołuje się automatycznie, jako reakcja na wcześniej określone zdarzenia występujące na tej tabeli.

Do zdarzeń, które powodują wykonanie triggera zalicza się:

  • INSERT – dodanie nowego rekordu do bazy danych,
  • UPDATE – aktualizacja istniejącego rekordu w bazie danych,
  • DELETE – usunięcie rekordu z bazy danych.

Trigger może być ustawiony do wywołania:

  • przed (BEFORE) lub
  • po (AFTER)

zdarzeniu, które go wywołuje.

Warto pamiętać, że triggery nie mają możliwości zatwierdzania transakcji (commit) ani nie mogą ich wycofywać (rollback). Są aktywowane tylko przez SQL.

W MySQLu triggery występują od wersji 5.0.2.

Jako przykład użycia posłużę się następującą sytuacją.
Istnieją dwa systemy. Pierwszy to sklep, do którego mogą się rejestrować nowi użytkownicy różnego typu (prywatni, firmy, zagraniczni itp). Drugi system (z osobną bazą danych) to aplikacja typu CMS. Obie bazy danych tj. shop_db i cms_db znajdują się na tym samym serwerze.
W przykładzie biorą udział poniższe 3 tabele:

  • shop_db.clients tabela przechowująca użytkowników sklepu internetowego (id, first_name, last_name, city…itd.)
  • shop_db.client_type tabela przechowująca rodzaj klientów (id, type)
  • cms_db.users tabela przechowująca użytkowników aplikacji CMS (id, first_name, last_name, city…shop_id itd.).

Założenie jest następujące: podczas rejestracji nowego użytkownika sklepu należy również dodać go do systemu CMS. Aby to zrobić można wykorzystać poniższy trigger:

use shop_db;

drop trigger if exists after_insert_clients;
DELIMITER $$

create trigger after_insert_clients
  after insert on clients
    for each row
      BEGIN
    # definiowanie zmiennych
        set @id = new.id;
        set @first_name = new.first_name;
        set @last_name = new.last_name;
        set @city = new.city;
        set @zip_code = new.zip_code;
        set @street = new.street;
        set @type_client = (SELECT type FROM shop_db.client_type where id = new.client_type_id);
        set @create_date = new.create_date;
        set @email = new.email;

        INSERT INTO cms_db.users (
                        first_name ,last_name, city ,zip_code ,street,type,create_date,email, shop_id) VALUES (
                        @first_name,@last_name,@city, @zip_code, @street, @type_client, @create_date, @email, @id);

    END$$

Od tego momentu za każdym razem gdy do sklepu zarejestruje się nowy użytkownik zostanie on także umieszczony w bazie danych systemu CMS w tabeli cms_db.users.

Aby upewnić się, czy trigger został poprawnie umieszczony w bazie danych można to zrobić za pomocą zapytania:

SHOW TRIGGERS

Zależnie od potrzeb, można oczywiście tworzyć bardziej rozbudowane triggery. Poniżej znajduje się instrukcja if-else, która, jeżeli select zwróci przynajmniej jeden rekord zostaje przypisana do zmiennej @regions wartość ‚Hessen‚, w przeciwnym wypadku ‚Other‚.

set @city = 'Frankfurt'; 
IF ((SELECT * FROM `shop_db`.`clients` where `city` like @city) > 0 ) THEN
  set @regions = 'Hessen';
ELSE 
  set @regions = 'Other';
END IF;

Dużym ułatwieniem przy pisaniu triggerów (lub funkcji proceduralnych) w MySQLu jest korzystanie z edytorów do edycji zapytań SQL takich jak np. MySQL Workbench.

Jako bonus dodam link (w języku angielskim), który również opisuje sposób tworzenia triggerow (on update oraz on insert):
http://www.freemindsystems.com/blog/post/mysql-triggers-a-practical-example

Poniżej znajduje się cały kod, który można sobie pobrać i przetestować na własnych komputerach (struktura tabel bazy danych wraz z triggerem):

USE shop_db;

--
-- Table structure for table `clients`
--

DROP TABLE IF EXISTS `clients`;
CREATE TABLE IF NOT EXISTS `clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL,
  `zip_code` varchar(100) NOT NULL,
  `street` varchar(120) NOT NULL,
  `client_type_id` int(11) NOT NULL,
  `create_date` date NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Triggers `clients`
--

DROP TRIGGER IF EXISTS `after_insert_clients`;
DELIMITER //
CREATE TRIGGER `after_insert_clients` AFTER INSERT ON `clients`
 FOR EACH ROW BEGIN
	# definiowanie zmiennych
        set @id = new.id;
        set @first_name = new.first_name;
        set @last_name = new.last_name;
        set @city = new.city;
        set @zip_code = new.zip_code;
        set @street = new.street;
        set @type_client = (SELECT type FROM shop_db.client_type where id = new.client_type_id);
        set @create_date = new.create_date;
        set @email = new.email;

        INSERT INTO cms_db.users (
                        first_name ,last_name, city ,zip_code ,street,type,create_date,email, shop_id) VALUES (
                        @first_name,@last_name,@city, @zip_code, @street, @type_client, @create_date, @email, @id);

    END
//
DELIMITER ;

--
-- Table structure for table `client_type`
--

DROP TABLE IF EXISTS `client_type`;
CREATE TABLE IF NOT EXISTS `client_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

USE cms_db;
--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL,
  `zip_code` varchar(100) NOT NULL,
  `street` varchar(120) NOT NULL,
  `type` varchar(100) NOT NULL,
  `create_date` date NOT NULL,
  `email` varchar(100) NOT NULL,
  `interest` varchar(200) NOT NULL,
  `shop_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;