Czasami potrzebujemy śledzić zmiany wykonywane w jakiejś tabeli w bazie danych. Można to zaimplementować w aplikacji, jednak język SQL dysponuje mechanizmami pozwalającymi zrealizować to zadanie bezpośrednio w bazie. Triggery czyli wyzwalacze wykonywane przed lub po operacji na danych posłużą do stworzenia mechanizmu logowania zmian w tabeli.

Załóżmy, że mamy następującą tabelę z danymi:

Stworzyliśmy ją za pomocą poniższego zapytania SQL:

CREATE TABLE `tabela` (
  `id` int NOT NULL AUTO_INCREMENT,
  `kolumna1` varchar(16) NOT NULL,
  `kolumna2` int DEFAULT NULL,
  PRIMARY KEY (`ID`)
);

Tabela z logami

Do przechowywania informacji o zmianach w tej tabeli musimy stworzyć kolejną. Poza kolumnami znajdującymi się w tabeli źródłowej trzeba do niej dodać informacje o rodzaju modyfikacji (dodanie, aktualizacja, skasowanie). Biorąc pod uwagę, że w może wystąpic jeden z trzech rodzajów modyfikacji kolumna ta będzie miała typ ENUM. Przydatna będzie też informacja o czasie wykonania zmiany. Zatem tabela z logami zmian będzie wyglądała następująco:

Tabelę tą utworzymy za pomocą poniższego zapytania SQL:

CREATE TABLE `tabela_historia` (
  `id` INT NOT NULL AUTO_INCREMENT AUTO_INCREMENT,
  `id_tabela` INT NOT NULL,
  `zmiana` enum('insert','update','delete') NOT NULL,
  `czas` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `kolumna1` varchar(16) DEFAULT NULL,
  `kolumna2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Wyzwalacze

W celu zapisywania informacji o wszelkich zmianach w tabeli użyjemy wyzwalaczy. Potrzebne są trzy triggery, będą one obsługiwały akcje INSERT, UPDATE, DELETE. W naszym przypadku każdy trigger będzie wywoływany po wykonaniu akcji (AFTER), ponieważ są one wywoływane po udanej operacji wykonania zmian. Wyzwalacze BEFORE byłyby aktywowane również w sytuacji, gdy operacja z jakiegoś powodu nie powiedzie się.

Wyzwalacze stworzymy poniższymi zapytaniami SQL:

DELIMITER //

CREATE TRIGGER `tabela_insert` AFTER INSERT ON `tabela`
FOR EACH ROW
BEGIN
    INSERT INTO tabela_historia (id_tabela, zmiana, kolumna1, kolumna2)
    VALUES (NEW.id, 'insert', NEW.kolumna1, NEW.kolumna2);
END//

CREATE TRIGGER `tabela_update` AFTER UPDATE ON `tabela`
FOR EACH ROW
BEGIN
    INSERT INTO tabela_historia (id_tabela, zmiana, kolumna1, kolumna2)
    VALUES (NEW.id, 'update', NEW.kolumna1, NEW.kolumna2);
END//

CREATE TRIGGER `tabela_delete` AFTER DELETE ON `tabela`
FOR EACH ROW
BEGIN
    INSERT INTO tabela_historia (id_tabela, zmiana, kolumna1, kolumna2)
    VALUES (OLD.id, 'delete', OLD.kolumna1, OLD.kolumna2);
END//

DELIMITER ;

W wyzwalaczach AFTER INSERT, AFTER UPDATE odwołujemy się do nowej wartości wiersza (np. NEW.id). Natomiast w triggerze AFTER DELETE po skasowaniu wiersza nie ma nowej wartości zatem następuje odwołanie do starej wartości wiersza (np. OLD.id).

Kod pokazany w tym wpisie jest zgodny z bazą MySQL. Jednak triggery są częścią standardów języka SQL i kod ten (po ewentualnej drobnej modyfikacji) powinien działać w dowolnej bazie obsługującej wyzwalacze.