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.
Wyzwalacze typu AFTER INSERT mogą wykonywać również DELETE FROM na innej tabel MYSQL. Pozdrawiam.