Ćwiczenie 9
WYZWALACZE BAZ DANYCH
Wyzwalacze bazy danych są procedurami składowanymi w bazie w powiązaniu z jej konkretną relacja. Z pojedynczą relacja może być związane wiele wyzwalaczy, natomiast pojedynczy wyzwalacz może być związany tylko z jedną relacją.
Wyzwalacze w zależności od tego, kiedy są uruchamiane dzielimy na dwie grupy:
before - uruchamiane przed poleceniemSQL,
after - uruchamiane po poleceniu SQL,
i mogę być wykonywane w wyniku poleceń: insert, update, delete.
Wyzwalacz definiowany jest za pomocą polecenia:
create [or replace] triger
np.:
create or replace trigger test
after delete on prac
declare
/*deklaracje zmiennych stałych, kursorów, wyjątków lokalnych wyzwalacza*/
begin
/*ciało wyzwalacza*/
dbms_output.put_line (`Krotka opisująca pracownika została usunięta');
end;
/
Ciało wyzwalacza może zawierać polecenia PL/SQL I SQL oraz wywołania podprogramów, z następującymi ograniczeniami:
nie wolno stosować poleceń DDL,
nie wolno stosować poleceń sterowania transakcjami SQL, takimi jak commint, rollback, savepoint ani też wywoływać podprogramów zawierających te polecenia.
Dla wyzwalaczy uruchamianych przez polecenia DML (insert, update, delete) można określić liczbę atrybutów relacji, których uaktualnienie uruchomi wyzwalacz. Np. dla relacji prac ma on postać:
SQL>create or replace trigger test
after insert or delete or update of etat, placa_pod on prac
begin
…….
end;
/
Możemy określić, czy wyzwalacz ma być uruchamiany dla każdej krotki relacji , czy też niezależnie od liczby krotek - jeden raz. Do tego celu służy klauzula for each row, która jeśli występuje w definicji wyzwalacza, to będzie on uruchamiany dla każdej krotki spełniającej warunek polecenia. W definicji wyzwalacza for each row może być umieszczona opcjonalna klauzula when, określająca dodatkowe warunki jego uruchomienia. Wyrażenie wartościujące klazuli musi być poleceniem SQL (bez podzapytań) i może ono zawierać dwa szczególne kwalifikatory new i old. Odwołania do tych kwalifikatorów tylko w klauzuli when nie poprzedzamy dwukropkiem. Np. w relacji prac jeśli placa_dod jest mniejsza od 100 pracownik dostaje podwyżkę.
SQL>create or replace trigger podwyżka
before update on prac
for each row
when (old
.placa_dod<100)
begin
…….
end;
/
W klauzuli when można stosować klasyczne operatory matematyczne (=,<-,>=,!=), logiczne (and, or, not) oraz operatory SQL(is null, like, between ... and, in) np.:
when(old.placa_dod between 100 and 300) …
Dla wyzwalacza uruchamianego poleceniem insert, kwalifikator new przyjmuje wartość null, natomiast uruchamianego poleceniem delete wartość null przyjmuje kwalifikator old. Wyzwalacz uruchamiany update ma dostęp do nowych i starych wartości niezależnie od sposobu uruchomienia.
Jako przykład użycia kwalifikatorów rozważmy wyzwalacz uruchamiany przed użyciem każdej krotki z relacji zesp. Pozwoli on na usunięcie jedynie tych zespołów, w których nikt nie pracuje.
SQL>create or replace trigger czy_zesp_ma_prac
before update on zesp
for each row
declare
l_prac number(2):=0;
begin
…….select count(*) into l_prac from prac where id_zesp=:old.id_zesp;
if l_prac>0
then
raise_application_error(-20000,'W tym zespole są zatrudnieni pracownicy');
endif;
end;
/
Uwaga! Wszystkie odwołania do kwalifikatorów new i old oprócz klauzuli when muszą być poprzedzone dwukropkiem.
Ciało wyzwalacza, które jest uruchamiane przez więcej niż jedno polecenie DML może zawierać tzw. predykaty warunkowe inserting, deleting i updating, które to umożliwiają wykonanie odpowiedniego fragmentu podprogramu, np. dla wyzwalacza zdefiniowanego poniżej
SQL>create or replace trigger test
before insert or update or delete on prac
begin
if inserting then …
endif;
if updating then …
endif;
if deleting then …
endif;
end;
/
polecenia predykatu updating zostaną wykonane tylko w przypadku uaktualnienie atrybutu placa_pod:
update prac set placa_pod=placa_pod*1.2 where id_zesp=10;
Zad. 1. Poniższy wyzwalacz test, zapisuje historią zmian relacji rachunki do relacji operacje_log, jest on uruchamiany po wpisaniu lub usunięciu krotki relacji rachunki lub przy aktualizacji atrybutu kwota. Opisać działanie wyzwalacza.
SQL>create or replace trigger test
after insert or update of kwota or delete on rachunki
for each row
begin
if inserting
then insert into operacje_log(lp, nr_rachunku, data1, typ_operacji)
values(seq_log.nextval, :new.nr_rachunku, sysdate,
'NOWY RACHUNEK);
elsif updating(`kwota')
then
if :old.kwota<:new.kwota
then insert into operacje_log
values(seq_log.nextval, :old.nr_rachunku, sysdate,
'WPŁATA', :old.kwota);
elsif :new.kwota=0 or :old.kwota> :new.kwota
then insert into operacje_log
values(seq_log.nextval, :old.nr_rachunku, sysdate,
'WYPŁATA', :old.kwota);
end if;
elsif deleting
then insert into operacje_log(lp, nr_rachunku, data1, typ_operacji, kwota)
values(seq_log.nextval, :old.nr_rachunku, sysdate,
'LIKWIDACJA', :old.kwota);
end if;
end;
/
Do zablokowania (odblokowania wyzwalaczy służy polecenie:
alter trigger nazwa_wyzwalacza disable [enable];
Wyzwalacz związany z daną relacją można zablokować (odblokować) poleceniem
alter table nazwa_relacji disable [enable] all triggers;
Opis wyzwalaczy zdefiniowanych przez użytkownika można odczytać przy pomocy zapytania do perspektywy systemowej USER_TRIGGERS.
Wyzwalacze usuwamy poleceniem:
drop trigger nazwa_wyzwalacza
Uwaga! W zależności od wersji serwera bazy danych Oracle z daną relacją może być związana różna ilość wyzwalaczy tego samego typu. Istnieje pewne ograniczenie stosowania wyzwalaczy zdefiniowanych z wykorzystaniem klauzuli for each row. W takim wyzwalaczu nie można odwoływać się do tej samej relacji, w której wyzwalacz został zdefiniowany.
Zad.2. Zdefiniować wyzwalacz nadający nowemu pracownikowi kolejny numer, który jest pobierany z licznika.
SQL>create or replace trigger generuj_numer …..
Zad 3. Zdefiniować relację historia_pracownik, przechowującą dane historyczne dotyczące pracowników, której schemat stanowi rozszerzenie schematu relacji prac o trzy następujące atrybuty: użytkownik, data_operacji, rodzaj_operacji. Pierwszy z nich przechowuje nazwę użytkownika, który dokonał zmian w zawartości relacji prac, drugi - datę dokonania tych zmian, a trzeci rodzaj operacji tj. `UPDATE' lub `DELETE'. Następnie zdefiniować wyzwalacz, który na skutek uaktualnienia wartości atrybutów etat, szef, placa_pod, placa_dod, id_zesp lub usunięcia krotek z relacji prac wpisze dane z przed modyfikacji lub usunięcia do relacji historia_pracownik. Wyzwalacz powinien również wpisać do tej relacji nazwę użytkownika, wykonującego operację, datę wykonania i rodzaj operacji.
SQL>create table historia_pracownik(
Id_prac number(4),
nazwisko varchar2(12),
etat varchar2(10),
id_szefa number(4),
zatrudniony date,
placa_pod number(6,2),
placa_dod number(6,2),
id_zesp number(2),
uzytkownik varchar2(12),
data_operacji date,
rodzaj_operacji varchar2(6));
SQL>create or replace trigger historia …..
………
end historia;
/
Zad. 4. Za pomocą wyzwalacza zdefiniować ograniczenie integralnościowe zapewniające, czy numer zespołu dla wprowadzanej lub uaktualnianej krotki pracownika jest numerem jednego z istniejących zespołów.
SQL>create or replace trigger czy_zesp_istnieje …..
Zad. 5. Poniższy wyzwalacz sprawdza, czy na skutek uaktualnienia krotek relacji prac, pensja pracownika pozostaje mniejsza od pensji jego przełożonego. W przypadku, gdy tak nie jest odwołuje transakcję. Czy wyzwalacz funkcjonuje poprawnie?
SQL>create or replace trigger pensja
after update of placa_pod on prac
for ich row
declare
pensja_szefa number(4);
begin
select placa_pod into pensja_szefa from prac where numer=:old.id_szefa;
if :new.placa_pod>pensja_szefa
then raise_application_error(
-20020,'Płaca pracownika > płacy szefa - nie dopuszczalne!');
endif;
end;
/
Po wykonaniu polecenia:
update prac set placa_pod=4000 where id_prac=1080;
LABORATORIUM z BAZ DANYCH
ORACLE
2