Oracle SQL - Triggery - Wyzwalacze

Trigery są to automatycznie procedury wywoływane przed i po z góry określonych operacjach na obiektach lub samej bazie danych. Są niejako rozwinięciem idei więzów inegralności (primary key, foreign key, unique, check, not null). Trigery oprogramowują stałe czynności. Często stosuje się je jako bezpieczniejszy zamiennik do klucza forign key on delete cascade. W trigerach można wykonać dodatkowe akcje monitorujące czy weryfikujące.

Do podstawowych zalicza się trigery na update, insert i delete z tabeli.

CREATE OR REPLACE TRIGGER trg_xyz_bd
BEFORE DELETE ON xyz
REFERENCING OLD AS old NEW AS new
for each row
DECLARE
  
BEGIN
  -- ... tu dowolna akcja np:
  insert into log values (:old.id, :old.val);
EXCEPTION
  when too_many_rows then
    ...
  when others then
    ...
END;

:old - zwyczajowo oznacza wiersz z przed zmiany
:new - zwyczajowo oznacza wiersz po zmianie

Te trzy postawowe typy trigerów mogą być wywoływane albo przed lub po akcji np

before delete
before insert 
before update 
after delete
aber insert
after update

Aby uświadomić sobie różnicę pomiędzy trigerami typu after i before można posłużyć się przykładami. Np insert.

Czy jeśli chcemy zmodyfikować wartość przy insercie da się to zrobić w trybie after? Nie! Należy w trybie before zmienić dla danej kolumny wartość w :new. Np

CREATE OR REPLACE TRIGGER trg_xyz_bi
BEFORE INSERT ON xyz
REFERENCING OLD AS old NEW AS new
for each row
BEGIN
  if :new.wynagrodzenie > 10000 then
   :new.wynagrodzenie := 10000;
  end if;
 
  :new.telefon := REGEXP_REPLACE(:new.telefon, '[^[:digit:]]','');
EXCEPTION
END;

Trigger na kolumnę - update

Triger typu before|after update pozwala na wyspecyfikowanie której kolumny [kolumn] ma dotyczyć. A więc przy uaktualnieniach którch kolumn ma być wywoływany. Można również wprowadzać klauzulę when, która ogranicza częstoś wywoływania trigera.

create or replace trigger trg_abc_telefon
after update of telefon on abc
for each row
when (new.telefon is not null)
declare
begin
  :new.telefon := REGEXP_REPLACE(:new.telefon,'[^[:digit:]]','');
EXCEPTION
end;

multi trigger

czyli trigger na kilka różnych akcji

create or replace trigger xyz_validator
after delete or update or insert on xyz
for each row
begin
  if deleting then
   -- ...
 end if;
 
  if updating then
   -- ...
 end if;
 
  if inserting then
   -- ...
 end if;
EXCEPTION
  ...
end;

Czego nie wolno w trigger-ach

Przede wszystkimi nie wolno specjalnie "grzebać" w tabeli na której dany trigger został włączony. Np. jeśli zmieniamy dane - to jak trigger zliczający sumujący wartości miałby zachować spójność wyników w dodatku na który moment... Nie da się tego zrobić, więc możliwości wykonania podobnych zapytań nie ma. Tym bardziej operacje update na bieżącej tabeli są niedozwolone - update by wywoływał rekurencyjne wywołanie trigger-a [after/before] update.

Tak więc trigger nie może czytać ani zmieniać danych w tabeli modyfikowanej (nie licząc oczywiście tego co kryje się pod zmiennymi :old i :new)
Jeśli istnieją tabele powiązane z tabelą modyfikowaną kluczami obcymi to i również nie wolno w triggerze modyfikować kolumny zawierającej ten klucz

Oba wymienione wyżej punkty tyczą się triggerów FOR EACH ROW (wyjątkiem jest insert dla pojedynczego wiersza).

Nie wolno stosować też commit oraz rollback - w ogólności żadnych poleceń związanych z transakcjami. Wyjątkowo jest to możliwe jeśli commit/rollback znajduje się wewnątrz transakcji autonomicznej.

Na bazie danych Oracle 10g można włączyć kilka jednakowych triggerów. Tzn kilka trigerów zawierających np after update on xyz. Baza danych nie będzie protestować. Niestety kolejność ich wykonania będzie przypadkowa (dopiero wersja Oracle 11g sobie z tym radzi).

Data ostatniej modyfikacji wpisu: 2010.03.06 20:58:23.