Wykład 14

Transact-SQL – wyzwalacze

1. Wstęp

Wyzwalacze bazy danych są to procedury wywoływane (odpalane) przez system przy zajściu odpowiedniego zdarzenia dotyczącego tabel w bazie danych. Będą więc miały budowę podobną do procedur składowanych, lecz nie będziemy mogli ich uruchomić samodzielnie, poleceniem EXECUTE, lecz będą uruchamiane automatycznie.

Wyzwalacze bazy danych służą do:

• Oprogramowania więzów spójności. Mogą stanowić uzupełnienie i rozszerzenie możliwości oferowanych przez deklaratywne więzy spójności. Ze względu na wydajność oraz łatwość implementacji zaleca się stosowanie wyzwalaczy wyłącznie tam, gdzie deklaratywne więzy spójności są niewystarczające.
• Oprogramowania stałych czynności, wykonywanych w bazie danych. Wyzwalacz uruchomi się zawsze przy zajściu określonego zdarzenia, niezależnie od tego, kto i w jaki sposób uruchomił instrukcję.

Wyzwalacze zostały wprowadzone do Standardu SQL:1999, aczkolwiek sposób ich definiowania i wykorzystania w Transact-SQL i PL/SQL znacznie się różni.

Wyzwalacz będzie zawsze związany z określoną tabelą oraz uruchamiany przez jedną lub więcej instrukcji DML (INSERT, UPDATE, DELETE).

2. Tworzenie wyzwalacza

Analogicznie, jak w przypadku procedur składowanych, możemy utworzyć wyzwalacz instrukcją CREATE, a zmodyfikować istniejący poleceniem ALTER, znajdując się w oknie kodu. Interfejs Management Studio wyświetla wyzwalacze po rozwinięciu wybranej tabeli oraz gałęzi Triggers. Z tego miejsca możemy je też tworzyć i modyfikować.

Składnia:

CREATE TRIGGER nazwa_wyzwalacza 
ON tabela 
FOR instrukcje_DML 
AS
instrukcje Transact-SQL

Instrukcje DML, które mogą wywołać wyzwalacz to:

• INSERT
• UPDATE
• DELETE

Po słowie FOR możemy napisać jedną, dwie lub wszystkie trzy instrukcje (oddzielone przecinkami).

Wyzwalacz odpalany jest zawsze PO wykonaniu wywołującej go instrukcji DML. Jest jednak odpalany w obrębie tej samej transakcji, co wywołująca go instrukcja. Dzięki temu możemy wycofać efekty tej instrukcji poleceniem ROLLBACK.

Odwołania do starych i nowych wartości modyfikowanych danych są możliwe dzięki wirtualnym tabelom o nazwach INSERTED i DELETED, zawierających odpowiednio nowe i stare wartości. Aby się do nich odwołać, musimy użyć instrukcji SELECT tak, jakby były to normalne tabele bazy danych. W przypadku, gdy wyzwalacz został uruchomiony przez instrukcję INSERT, tylko tabela INSERTED będzie zawierała rekordy. Tabela DELETED będzie pusta. Gdy odpalającą wyzwalacz instrukcją jest DELETE, tylko DELETED zawiera rekordy. W przypadku UPDATE obie tabele zawierają rekordy: INSERTED zawiera nowe wartości (te, na które zmieniamy), a DELETED stare (nadpisane).

Tabele te są tylko do odczytu, czyli modyfikacja danych bezpośrednio w nich nie jest możliwa. Jeśli więc chcemy wewnątrz wyzwalacza wprowadzić modyfikację danych, musimy wykonywać instrukcje DML na rzeczywistych tabelach. Pamiętajmy, że dane w tych tabelach są danymi już zaktualizowanymi przez instrukcję uruchamiającą wyzwalacz.

Istniejące wyzwalacze mogą być włączane i wyłączane instrukcją:

ENABLE / DISABLE TRIGGER nazwa_wyzwalacza ON nazwa_tabeli

3. Przykłady

W poniższym przykładzie wyzwalacz ma za zadanie nie pozwolić usunąć wiersza z tabeli. Będzie uruchamiany automatycznie po każdym wywołaniu instrukcji DELETE na tabeli „emp”. Ponieważ jednak wyzwalacz jest uruchamiany po wykonaniu operacji DELETE, musimy przy pomocy instrukcji ROLLBACK wycofać wprowadzone zmiany. Pamiętajmy, że wyzwalacz jest uruchomiony w tej samej transakcji co instrukcja.

CREATE TRIGGER wyzw ON Emp
FOR DELETE
AS
ROLLBACK

W kolejnym przykładzie chcielibyśmy, aby nowo wstawiani pracownicy nie mogli mieć zarobków równych zero. Gdy taki pracownik zostanie wprowadzony, wyzwalacz wycofa zmiany i zgłosi błąd (oczywiście można to osiągnąć używając więzów spójności typu CHECK).

CREATE TRIGGER wyzw ON Emp FOR INSERT
AS
DECLARE @sal DECIMAL(6,2)
SELECT @sal = sal FROM inserted
IF @sal = 0 
BEGIN
   ROLLBACK
   RAISERROR('Nie można wstawić pracownika z pensją równą zero!',1,2)
END

Powyższy przykład nie zawsze zadziała poprawnie. W Transact-SQL wyzwalacz jest uruchamiany dla całej instrukcji DML. Instrukcja DML może operować na 1 wierszu (taka sytuacja jest prosta) lub na wielu wierszach. W przypadku wielu wierszy tabele INSERTED i/lub DELETED będą również zawierać wiele wierszy. Aby taką sytuację obsłużyć, będziemy musieli zastanowić się nad kodem, który zadziała poprawnie dla dowolnej liczby wierszy. Czasem będziemy musieli przejrzeć wszystkie rekordy tabel INSERTED i/lub DELETED przy pomocy kursora.

Powyższy przykład będzie więc działał poprawnie tylko gdy instrukcja INSERT która go wywołała, operowała na jednym wierszu.

Rozwiązanie bardziej uniwersalne:

CREATE TRIGGER pensja ON emp FOR INSERT
AS 
BEGIN 
IF EXISTS (SELECT 'x' FROM inserted WHERE sal = 0) 
BEGIN 
   ROLLBACK 
   RAISERROR ('Nie można wstawić pracownika z pensją równą 0', 1, 2) 
END

W tym przypadku, jeśli którykolwiek wiersz znajdujący się w wirtualnej tabeli INSERTED będzie miał nieprawidłową pensję, transakcja zostanie wycofana.

W kolejnym przykładzie chcielibyśmy, aby wstawiani lub modyfikowani pracownicy mieli pensję większą niż 1000. Do rozwiązania tego zadania wystarczyłyby deklaratywne więzy typu CHECK. Więzy spójności wycofają jednak całą instrukcję. My chcielibyśmy, aby poprawne rekordy zostały wstawione lub zmodyfikowane, a odrzucone tylko te, których pensja jest za mała.

CREATE TRIGGER zarobki
ON emp
FOR INSERT, UPDATE
AS
DECLARE @ile INT, @old_sal INT, @new_sal INT, @empno INT
SELECT @ile = COUNT(*) from deleted
IF @ile = 0 BEGIN -- jeśli 0 rekordów w deleted, to był to INSERT
   DECLARE kurs CURSOR FOR SELECT empno, sal FROM inserted
   OPEN kurs
   FETCH NEXT FROM kurs INTO @empno, @new_sal
      WHILE @@FETCH_STATUS = 0 BEGIN
      IF @new_sal < 1000
         DELETE FROM emp WHERE empno = @empno
      FETCH NEXT FROM kurs INTO @empno, @new_sal
   END
   CLOSE kurs
   DEALLOCATE kurs
END ELSE BEGIN -- UPDATE
   DECLARE kurs CURSOR FOR SELECT empno, sal FROM inserted
   OPEN kurs
   FETCH NEXT FROM kurs INTO @empno, @new_sal
   WHILE @@FETCH_STATUS = 0 BEGIN
      IF @new_sal < 1000 BEGIN
         SELECT @old_sal = sal FROM deleted WHERE empno = @empno
         UPDATE emp set sal = @old_sal WHERE empno = @empno
      END
      FETCH NEXT FROM kurs INTO @empno, @new_sal
   END
   CLOSE kurs
   DEALLOCATE kurs
END

W przykładzie tym następuje sprawdzenie, na podstawie liczby rekordów w tabelach INSERTED i DELETED, jaka instrukcja spowodowała uruchomienie wyzwalacza. W przypadku, gdy był to INSERT, przechodzimy kursorem po tabeli INSERTED i usuwamy wiersze, które nie spełniają warunku. W przypadku UPDATE, również przechodzimy kursorem po tabeli INSERTED, ale tym razem, dla niepoprawnych modyfikacji pobieramy ich starą, nadpisaną wartość i zmieniamy z powrotem na nią.

Zadanie to da się również wykonać bez użycia kursorów:

CREATE TRIGGER zarobki
ON emp
FOR INSERT, UPDATE
AS
DECLARE @ile INT
SELECT @ile = COUNT(*) from deleted
IF @ile = 0 BEGIN -- jeśli 0 rekordów w deleted, to był to INSERT
   DELETE FROM emp 
   WHERE sal < 1000 AND empno IN (SELECT empno FROM inserted)
END ELSE BEGIN -- UPDATE
   UPDATE emp
   SET sal = deleted.sal
   FROM inserted, deleted
   WHERE emp.empno = inserted.empno
   AND emp.empno = deleted.empno
   AND inserted.sal < 1000
END

Tym razem również sprawdzamy, która instrukcja spowodowała uruchomienie wyzwalacza. W przypadku INSERT usuwamy rekordy o pensji mniejszej niż 1000, których identyfikatory znajdują się w tabeli INSERTED, a więc te, które właśnie są wstawiane. W przypadku UPDATE używamy skorelowanej instrukcji UPDATE, która na bazie tabel INSERTED i DELETED zidentyfikuje i przywróci niepoprawnie zmodyfikowane dane. Skorelowany UPDATE będzie omówiony dokładniej w wykładzie 15.

Dotychczasowe przykłady dotyczyły oprogramowania więzów spójności przy pomocy wyzwalaczy. Jak wspomniano na początku tego wykładu, wyzwalacze są też używane do automatyzacji stałych czynności w bazie danych. Kolejny przykład prezentuje tego typu użycie wyzwalacza.

W naszym przykładzie będzie istniała tabela "budzet" zawierająca sumaryczne zarobki wszystkich pracowników. Tabela będzie zawierała jeden wiersz i jedną kolumnę. Możemy ją utworzyć następującym poleceniem:

CREATE TABLE budzet (wartosc INT NOT NULL)

Wstawimy początkową wartość przy pomocy polecenia:

INSERT INTO budzet (wartosc)
SELECT SUM(sal) FROM emp

Od tej chwili chcielibyśmy, aby wyzwalacz pilnował aktualności danych w tabeli „budzet”, czyli po każdym wstawieniu, usunięciu lub zmianie danych pracowników, wartość budżetu powinna być zaktualizowana. Rozwiązanie naiwne polegałoby na policzeniu sumy od początku wewnątrz wyzwalacza uruchamianego każdą instrukcją DML na tabeli „emp”. Byłoby to rozwiązanie bardzo mało wydajne. My natomiast policzymy różnicę pensji na podstawie tabel INSERTED oraz DELETED i zaktualizujemy budżet o tą różnicę:

CREATE TRIGGER akt_budzet
ON emp
FOR INSERT, UPDATE, DELETE
AS
DECLARE @zmniejsz INT, @zwieksz INT
SELECT @zmniejsz = SUM(sal) FROM deleted
SELECT @zwieksz = SUM(sal) FROM inserted
UPDATE budzet SET wartosc = wartosc - ISNULL(@zmniejsz,0) + ISNULL(@zwieksz,0)

4. Wyzwalacze w PL/SQL

W serwerze Oracle zarówno składnia, jak i zasada działania wyzwalaczy, dość znacznie się różnią. Najważniejsze różnice:

• W PL/SQL wyzwalacz może być uruchomiony po wykonaniu instrukcji (jak w Transact-SQL), ale może też być uruchomiony przed wykonaniem instrukcji. Dzięki temu nie musimy wycofywać dokonanych zmian, lecz do nich nie dopuszczamy. Rodzaj wyzwalacza określimy umieszczając BEFORE lub AFTER w jego nagłówku. Te pierwsze będą nadawały się lepiej do oprogramowania więzów spójności, a te drugie do automatyzacji.
• Ucząc się na temat wyzwalaczy w Transact-SQL dowiedzieliśmy się, że są uruchamiane dla całej instrukcji DML nawet, gdy operuje ona na wielu wierszach. W Oracle mamy również taką możliwość, lecz możemy też zadeklarować wyzwalacz z dopiskiem FOR EACH ROW dzięki czemu dla każdego wiersza uruchomi się osobna instancja wyzwalacza. Jest to sytuacja dużo łatwiejsza dla programisty, gdyż pisząc kod może skupić się na obsłudze tylko jednego wiersza, a nie całej tabeli (jak INSERTED i DELETED).
• Odwołanie do starych i nowych wartości odbywa się nie przez wirtualne tabele INSERTED i DELETED, lecz poprzez zmienne mające postać :NEW.nazwa_pola i :OLD.nazwa_pola. Są one dostępne wyłącznie w wyzwalaczach zadeklarowanych z dopiskiem FOR EACH ROW.
• Wygodną konstrukcją są zmienne logiczne INSERTING, UPDATING i DELETING, które określają, jaka instrukcja spowodowała uruchomienie wyzwalacza.

Instrukcja tworząca wyzwalacz w PL/SQL ma następującą postać:

CREATE [OR REPLACE] TRIGGER nazwa
{BEFORE / AFTER} specyfikacja instrukcji
ON tabela
[FOR EACH ROW]
   blok PL/SQL

Specyfikacja instrukcji może składać się z wielu instrukcji oddzielonych przez OR, np. BEFORE INSERT OR UPDATE. Dla UPDATE możemy określić listę kolumn, których modyfikacja uruchomi wyzwalacz, np. UPDATE OF sal.

Oto przykładowy wyzwalacz, który nie pozwoli usuwać rekordów z tabeli "salgrade":

CREATE OR REPLACE TRIGGER nie_usuwaj
BEFORE DELETE
ON salgrade
BEGIN
   raise_application_error(-20500, 'Nie wolno usuwac z SALGRADE');
END;

A oto wyzwalacz pilnujący, aby wstawiana lub modyfikowana pensja nie mogła mieć wartości mniejszej niż 1000:

CREATE OR REPLACE TRIGGER pensja1000
BEFORE INSERT OR UPDATE OF sal
ON emp
FOR EACH ROW
BEGIN
   IF :NEW.sal < 1000 THEN
      raise_application_error(-20500, 'Za mala pensja');
   END IF;
END;

Zwróćmy uwagę na klauzulę FOR EACH ROW, dzięki której wyzwalacz uruchomi się wielokrotnie w przypadku, gdy modyfikacja dotyczy wielu wierszy. Jak widać, pozwala to napisać znacznie prostszy kod, niż w przypadku wyzwalacza uruchamianego raz dla całej instrukcji.

W kolejnym przykładzie pokazane jest rozwiązanie zadania dotyczącego tabeli "budżet" (ostatni przykład punktu 3).

CREATE OR REPLACE TRIGGER akt_budzet
AFTER INSERT OR UPDATE OR DELETE
ON emp
FOR EACH ROW
BEGIN
   IF INSERTING THEN
      UPDATE budzet SET wartosc = wartosc + :NEW.sal;
   ELSIF UPDATING THEN
      UPDATE budzet SET wartosc = wartosc + :NEW.sal - :OLD.sal;
   ELSIF DELETING THEN
      UPDATE budzet SET wartosc = wartosc - :OLD.sal;
   END IF;
END;