Wyzwalacze

Wyzwalacze są to bloki PL/SQL, których wywołanie następuje automatycznie w przypadku zajścia jakiegoś zdarzenia.

Definiując wyzwalacz określamy, w jakim przypadku ma się on uruchomić. W wersji Oracle poniżej 8i można było zdefiniować tylko wyzwalacze powiązane z operacjami DML na tabelach. W wersji 8i możliwe jest powiązanie wyzwalaczy z operacjami DML na widokach oraz ze zdarzeniami systemowymi.

Wyzwalacze DML

Tworzenie, usuwanie

CRAETE [OR REPLACE] TRIGGER nazwa_wyzwalacza
{BEFORE|AFTER} 
{INSERT|UPDATE|DELERE|INSERT OR UPDATE OR DELETE}
ON nazwa_tabeli
[FOR EACH ROW [WHEN warunek]]
ciało_wyazwalacza;

DROP TRIGGER nazwa_wyzwalacza;

ALTER TRIGGER nazwa_wyzwalacza {DISABLE|ENABLE};
                        

Kolejność wykonywania się wyzwalaczy DML

Jeżeli na pewnej tabeli są założone wszystkie rodzaje wyzwalaczy, to ich kolejność jest ściśle określona.

Przykład 1

-- zostaje wykonana instrukcja:
UPDATE tabela1 
   SET kol1 = wartość1
 WHERE kol1 = wartość2;
-- Instrukcja ta spowodowała modyfikację 3 wierszy w tabela1 
-- Kolejność wykonywania wyzwalaczy jest następujące
BEFORE UPDATE ON tabela1
  BEFORE UPDATE ON tabela1 FOR EACH ROW
   -- modyfikacja jednego wiersza
  AFTER UPDATE ON tabela1 FOR EACH ROW
  BEFORE UPDATE ON tabela1 FOR EACH ROW
   -- modyfikacja jednego wiersza
  AFTER UPDATE ON tabela1 FOR EACH ROW
  BEFORE UPDATE ON tabela1 FOR EACH ROW
   -- modyfikacja jednego wiersza
  AFTER UPDATE ON tabela1 FOR EACH ROW
AFTER UPDATE ON tabela1
                        

Pseudorekordy dostępne w wyzwalaczach

W każdym wyzwalaczu FOR EACH ROW dostępne są dwie zmienne rekordowe, które zawierają informacje o wierszu, który aktualnie jest przetwarzany. Zmienne te nazywają się :old i :new i zawierają odpowiednio w zależności od typu wyzwalacza wiersz usuwany, dodawany, bądź modyfikowany.

Zmienne rekordowe mogą być wykorzystywane nie tylko do odczytu, ale w wyzwalaczach BEFORE INSERT i BEFORE UPDATE mogą być również modyfikowane, co skutkuje wstawieniem do tabeli odpowiednio zmodyfikowanych danych. Jeżeli w wyzwalaczach DML zostanie wywołany błąd, to operacja, która spowodowała wyzwolenie tego wyzwalacza zakończy się niepowodzeniem i zgłosi błąd, który został wywołany w wyzwalaczu.

Predykaty

Jeden wyzwalacz może być powiązany z wszystkimi operacjami: INSERT, UPDATE i DELETE.

Dzięki predykatom wewnątrz kodu wyzwalacza można rozpoznać, jaka operacja spowodowała uruchomienie wyzwalacza.

Istnieją trzy predykaty: INSERTING, UPDATING i DELETING i przybierają one odpowiednio wartość TRUE bądź FALSE w zależności od tego, jaka operacja spowodowała uruchomienie wyzwalacza.

-- Obsługa modyfikacji pozycji zamówień
CREATE OR REPLACE TRIGGER tg_pzm_modif
  AFTER UPDATE OR DELETE OR INSERT ON pozycje_zamowien
  FOR EACH ROW
DECLARE
  v_dummy  number(1);
  v_cena   towary.tow_cena%TYPE;
BEGIN
  -- Czy modyfikacja nie dotyczy zamówienia zrealizowanego?
  BEGIN
    SELECT 1 INTO v_dummy
      FROM zamowienia
     WHERE zam_data_realizacji IS NOT NULL
       AND (zam_id = :old.pzm_zam_id 
            OR zam_id = :new.pzm_zam_id);
    RAISE too_many_rows;
  EXCEPTION
    WHEN no_data_found THEN
      NULL;
    WHEN too_many_rows THEN
      raise_application_error(-20002,
              'Nie można modyfikować zrealizowanych zamówień');
  END;
  -- Odpowiednie zmodyfikowanie kwoty zamówienia
  IF INSERTING THEN –- dodanie nowej pozycji do zamówienia
    SELECT tow_cena INTO v_cena
      FROM towary
     WHERE tow_id = :new.pzm_tow_id;
    UPDATE zamowienia 
       SET zam_kwota = zam_kwota + (v_cena * :new.pzm_ilosc)
     WHERE zam_id = :new.pzm_zam_id;
  ELSIF DELETING THEN -– usunięcie pozycji z zamówienia
    SELECT tow_cena INTO v_cena
      FROM towary
     WHERE tow_id = :old.pzm_tow_id;
    UPDATE zamowienia 
       SET zam_kwota = zam_kwota - (v_cena * :old.pzm_ilosc)
     WHERE zam_id = :old.pzm_zam_id;
  ELSE -- modyfikacja pozycji zamówienia
    SELECT tow_cena INTO v_cena
      FROM towary
     WHERE tow_id = :new.pzm_tow_id;
    UPDATE zamowienia 
       SET zam_kwota = zam_kwota + (v_cena * :new.pzm_ilosc)
     WHERE zam_id = :new.pzm_zam_id;
    SELECT tow_cena INTO v_cena
      FROM towary
     WHERE tow_id = :old.pzm_tow_id;
    UPDATE zamowienia 
       SET zam_kwota = zam_kwota - (v_cena * :old.pzm_ilosc)
     WHERE zam_id = :old.pzm_zam_id;
  END IF;
END;
                        

Predykaty

Wyzwalacze te mogą być powiązane tylko z perspektywami i wykonują się nie przed operacją DML, nie po operacji DML, ale zamiast operacji DML, i służą do tego, aby interpretować odpowiednio operacje na perspektywach, na których dotychczas nie było wolno wykonywać operacji DML.

CRAETE [OR REPLACE] TRIGGER nazwa_wyzwalacza
INSTEAD OF {INSERT|UPDATE|DELETE|INSERT OR UPDATE OR DELETE}
ON nazwa_widoku
[WHEN warunek]
ciało_wyazwalacza;
                        

Klauzula FOR EACH ROW jest opcjonalna i nic nie wnosi, ponieważ wszystkie wyzwalacze INSTEAD OF są wyzwalaczami na poziomie wiersza.

W wyzwalaczach INSTEAD OF dostępne są także zmienne rekordowe :old i :new oraz predykaty INSERTING, UPDATING i DELETING.

Przykład

-- stworzenie widoku
CREATE OR REPLACE VIEW kli_zam AS
  SELECT K.klt_id id_kl,
         decode(K.klt_typ,'IND',K.klt_nazwisko||' '||K.klt_imie,
                          K.klt_nazwa) klient, 
         Z.zam_opis opis, 
         Z.zam_id id_zam
    FROM klienci K, zamowienia Z
   WHERE K.klt_id = Z.zam_klt_id;
-- stworzenie odpowiedniego wyzwalacza
CREATE OR REPLACE TRIGGER tg_kli_zam_del
INSTEAD OF DELETE ON kli_zam
DECLARE
  v_ile binary_integer;
BEGIN
  -- usunięcie pozycji usuwanego zamowienia
  DELETE pozycje_zamowien
   WHERE pzm_zam_id = :old.id_zam;
  -- usunięcie zamówienia
  DELETE zamowienia
   WHERE zam_id = :old.id_zam;
  -- sprawdzenie, czy klienta ma jeszcze jakieś zamówienia
  SELECT count(*) INTO v_ile
    FROM zamowienia
   WHERE zam_klt_id = :old.id_kl;
  -- jeżeli nie ma więcej zamówień, to usunięcie klienta
  IF v_ile = 0 THEN
    DELETE klienci
     WHERE klt_id = :old.id_kl;
  END IF;
END;
                        

Wyzwalacze DDL

Wyzwalacze systemowe mogą być powiązane z operacjami DDL takimi jak CREATE, ALTER i DROP, oraz ze zdarzeniami systemowymi, takimi jak startup/shutdown serwer, logon/logof użytkownik i server error.

CREATE [OR REPLACE] TRIGGER [schemat.]nazwa
{BEFORE|AFTER}
{lista_zdarzeń_ddl|lista_zdarzeń_systemowych}
ON {DATABASE | SCHEMA}
[WHEN warunek]
ciało_wyzwalacza
                        

Nie dla wszystkich zdarzeń systemowych można założyć wyzwalacze BEFORE i AFTER.

Wyzwalacze systemowe mogą być definiowane na poziomie pojedynczego schematu lub całej bazy. Wyjątkiem jest startup i shutdown, które mogą być definiowane tylko na poziomie bazy.

W przypadku wyzwalaczy systemowych istnieje kilka funkcji pozwalających otrzymać informacje na temat zdarzenia, które zainicjowało uruchomienie wyzwalacza (analogiczne do :new, :old w wyzwalaczach DML i INSTEAD OF).

Przykład

-- logowanie wszystkich tworzonych obiektów na schemacie
CREATE OR REPLACE TRIGGER tg1
AFTER CREATE ON SCHEMA
BEGIN
  INSERT INTO ddl_log (user_id
               , obj_type
               , obj_name
               , obj_owner
               , cre_time)
       VALUES (USER
             , SYS.DICTIONARY_OBJ_TYPE
             , SYS.DICTIONARY_OBJ_NAME
             , SYS.DICTIONARY_OBJ_OWNER
             , SYSDATE);
END;
                        

Ograniczenia wyzwalaczy

Tabele modyfikowane

Istnieją pewne ograniczenia w wyzwalaczach dotyczące dostępu do tabel. Ważne jest aby rozróżnić pewne typy tabel.

Tabele modyfikowane (mutating tables) to te tabele, które są modyfikowane przez instrukcję DML, która spowodowała uruchomienie wyzwalacza. Są to także tabele podrzędne przyłączone do tabeli modyfikowanej kluczem obcym z opcją CASCADE DELETE.

Tabele ograniczone (constraints tables) to tabele, które muszą być odczytywane podczas modyfikowania danych w tabeli modyfikowanej. Praktycznie są to tabele powiązane kluczem obcym z tabelami modyfikowanymi.

• Żadne polecenie SQL w ciele wyzwalacza nie może czytać ani modyfikować tabeli modyfikowanej, zwłaszcza tabeli, na której jest wyzwalacz oparty.
• Żadne polecenie SQL w ciele wyzwalacza nie może czytać ani modyfikować kluczy unikalnych, głównych i obcych tabel ograniczonych. Możliwe jest modyfikowanie pozostałych kolumn.
  Ograniczenia powyższe dotyczą wszystkich wyzwalaczy na poziomie wierszy (FOR EACH ROW). Jeżeli Polecenie INSERT dotyczy tylko jednego wiersza, to w ciele wyzwalacza BEFORE i AFTER wyjątkowo można się odwoływać do tabeli, na której jest oparty wyzwalacz.

Transakcje

Ciało wyzwalaczy nie może zawierać żadnych poleceń związanych z transakcjami. Co za tym idzie, nie może wywoływać pośrednio podprogramów, które zawierają takie polecenia bezpośrednio lub pośrednio.