Trigger/Wyzwalacz w SQL opis, składnia i przykłady

Triger, zwany także wyzwalaczem jest to skrypt (fragment kodu) wykonywany w przypadku zajścia jakiegoś zdarzenia w bazie danych (np. dodania danych, ich modyfikacji, czy usunięcia).

Trigery dostępne są w następujących bazach danych:

Microsoft SQL Server,
PostgreSQL,
Sybase,
Oracle,
Firebird,
SQLite,
InterBase SQL,
MySQL

Istnieje kilka typów wyzwalaczy, tutaj skoncentrujemy się konkretnie na dwóch: BEFORE i AFTER.

Dla każdego typu istnieją trzy zdarzenia powodujące wykonanie wyzwalacza i są to:

AFTER DELETE – wykonanie wyzwalacza po operacji usunięcia rekordu
AFTER INSERT – wykonanie wyzwalacza po dodaniu rekordu
AFTER UPDATE – wykonanie wyzwalacza po zmodyfikowaniu rekordu

BEFORE DELETE – wykonanie wyzwalacza przed operacji usunięcia rekordu
BEFORE INSERT  – wykonanie wyzwalacza przed dodaniu rekordu
BEFORE UPDATE – wykonanie wyzwalacza przed zmodyfikowaniu rekordu

Konstrukcja:

CREATE TRIGGER nazwa_trigera
BEFORE INSERT ON -- zdarzenie określające kiedy triger zostanie wyzwolony
nazwa_tabeli -- tabela na której triger zostanie założony
FOR EACH ROW BEGIN
...  -- skrypt wykonywany przez triger
END

Przykład:

1. Utworzymy najpierw prostą tabelkę na której utworzymy triger. Będzie to tabelka która będzie przechowywała informacje o pracownikach.

CREATE
TABLE pracownik
(
id_pracownika INT(5) NOT NULL,
imie CHAR(20) NOT NULL,
nazwisko CHAR(50) NOT NULL,
pensja FLOAT(6,2) NOT NULL,
data_dodania DATETIME,
data_mod DATETIME,
pop_pensja FLOAT,
PRIMARY KEY (id_pracownika)
)

2. Teraz utworzymy trigera który automatycznie uzupełni nam datę dodania rekordu do bazy. Posłuży nam do tego triger data_dodania, którego utworzymy następującym poleceniem:

CREATE TRIGGER data_dodania
BEFORE INSERT ON pracownik
FOR EACH ROW BEGIN

SET NEW.data_dodania = now();

END

Wyjaśnienie: NEW.data_dodania oznacza nową wartość data_dodania. Triger wywołany przed operacją INSERT spowoduje, że przy dodaniu rekordu do tabeli pracownik wartość pola data_dodania przyjmie aktualną (funkcja now()) datę + czas.

3. Kolejnym przykładem będzie triger, który będzie uzupełniał datę ostatniej modyfikacji rekordu, a także wartość pola pop_pensja (poprzednia pensja) – w momencie kiedy pensja pracownika ulegnie zmianie.

CREATE TRIGGER modyfikacja
BEFORE UPDATE ON pracownik
FOR EACH ROW BEGIN

SET NEW.data_mod = now();

IF NEW.pensja!=OLD.pensja THEN
SET NEW.pop_pensja = OLD.pensja;
END IF;

END

Wyjaśnienie: Wykorzystana została funkcja warunkowa IF która sprawdza czy nowa pensja (NEW.pensja) pracownika jest różna od poprzedniej (OLD.pensja). Jeżeli warunek jest spełniony, pole pop_pesja przyjmie wartość poprzedniej pensji, zaś pole pensja przyjmie wartość aktualną.

Sprawdzenie:

1. Sprawdźmy teraz działanie funkcji triger z przykładu 2.

Wykonajmy teraz poniższe zapytanie:

INSERT INTO pracownik (id_pracownika, imie, nazwisko, pensja) VALUES (1, 'Jan', 'Kowalski', '3000');

i sprawdźmy zawartość tabeli pracownik:

select * from pracownik;

Otrzymamy:

1 Jan Kowalski 3000.00 2014-06-06 11:57:04 (null) (null)

Zatem triger uzupełnił pole data_dodania.

2. Sprawdźmy teraz działanie funkcji triger z przykładu 3. Wykonajmy aktualizację pensji:

UPDATE pracownik SET pensja = 3400 WHERE id_pracownika=1

i sprawdźmy zawartość tabeli pracownik:

select * from pracownik;

Otrzymamy:

1 Jan Kowalski 3400.00 2014-06-06 11:57:04 2014-06-06 12:03:40 3000.0

Triger uzupełnił pola z datą modyfikacji oraz poprzednią pensją.

 

Jeżeli macie jakieś pytania, bądź potrzebujecie jakiś przykład – proszę pisać w komentarzach.