Celem kolejnego ze zleceń jakie otrzymaliśmy jest napisanie rozbudowanego systemu ankiet z możliwością powrotu i zmiany odpowiedzi a co za tym idzie śledzeniem zmian.

Zanim talen wspomniał coś przy innej okazji nt. TRIGGERów w MySQL zapewne zrobił bym to używając kilku SELECT połączonych z UPDATE i INSERT. Jednak dzięki mechanizmowi TRIGGERów możemy to spłycić do jednego zapytania. Jak? Za chwilkę, najpierw o mechanizmie.

TRIGGERy to coś na kształt EVENTów - odpalają się w bazie kiedy zajdzie opisana przez programistę podczas ich tworzenia sytuacja - np, jakiś wiersz zostanie zmodyfikowany, usunięty lub dodany - pozawala nam to na jednokrotne zaprogramowanie takiego zdarzenia w MySQL i nie zawracanie sobie już tym głowy w warstwie aplikacji.

Przejdźmy do zadania - tworzymy sobie prostą tabelę:

CREATE TABLE IF NOT EXISTS `answers` (
        `question_id` int(11) NOT NULL,
        `user_id` int(11) NOT NULL,
        `answer_id` int(11) NOT NULL,
        UNIQUE KEY `question_id` (`question_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Jak widać, założyłem klucz UNIQUE na pola question_id i user_id - zapobiega to dodaniu wielu odpowiedzi na jedno pytanie przez tego samego użytkownika. Następnym krokiem jest stworzenie tabeli, która przechowywać będzie archiwalne odpowiedzi - kod jest prawie identyczny:

 CREATE TABLE IF NOT EXISTS `answers_old` (
        `question_id` int(11) NOT NULL,
        `user_id` int(11) NOT NULL,
        `answer_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Tabela nie posiada żadnych indexów, ponieważ każda odpowiedź mogła być zmieniona więcej niż raz, więc będziemy mieli więcej niż jedno archiwum.

Następnym krokiem jest stworzenie samego TRIGGERa:

DELIMITER //
        CREATE TRIGGER `test`.`archiwizuj` BEFORE UPDATE ON `test`.`answers`
                FOR EACH ROW BEGIN
                        INSERT INTO answers_old SET question_id = OLD.question_id, answer_id = OLD.answer_id, user_id = OLD.user_id;
                END
        //
DELIMITER ;

Przed utworzeniem samego TRIGERRA zmieniamy znak końca funkcji na coś innego niż standardowy średnik, by średniki w treści zapytania nie wykrzaczyły nam operacji. Jeżeli robicie to przez phpMyAdmin i po wydaniu polecenia zwrócony zostanie błąd, a macie pewność, że wasza baza obsługuje TRIGERRy sprawdźcie czy mimo wszystko nie został on pomyślnie utworzony - u mnie tak właśnie było, a ja jak głupi szukałem błędu. Cóż.

Teraz demonstracja:

 INSERT INTO `answers` SET `question_id` = 1, `user_id` = 1, `answer_id` = 1 ON DUPLICATE KEY UPDATE `answer_id` = 1;

Proste zapytanie, ustawia odpowiedź “1″ dla użytkownika “1″ i pytania “1″ - jeżeli istnieje już wpis użytkownika dla tego pytania, zaktualizuj odpowiedź na 1.

Po tym wywołaniu mamy jedynki w tabeli. Następne podejście:

INSERT INTO `answers` SET `question_id` = 1, `user_id` = 1, `answer_id` = 2 ON DUPLICATE KEY UPDATE `answer_id` = 2;

Użytkownik aktualizuje odpowiedź - pole `answer_id` zmieniło się na 2. Jak dotąd - nic nowego. Jednak jeżeli zajrzymy do tabeli `answers_old` zobaczymy tam wpis, który widzieliśmy poprzednio w `answers`. Jeszcze jedno zapytanie:

INSERT INTO `answers` SET `question_id` = 1, `user_id` = 1, `answer_id` = 3 ON DUPLICATE KEY UPDATE `answer_id` = 3;

W tabeli `answers` mamy odpowiedź numer 3, zaś w `answers_old` są już dwa wpisy z odpowiedziami 1 i 2.

Jak widać całość działa świetnie i zostało nam już tylko napisanie mechanizmu obsługującego informowanie administratora o zmianie odpowiedzi i wyświetlanie historii. Oczywiście ten przykład odnosi się nie tylko do ankiet, ale do wszelkich rodzaju danych, których stare wersje chcemy zachowywać w bazie danych - np. na potrzeby archiwizowania starej wersji strony w celu jej późniejszego odtworzenia etc etc.