PL/SQL
Zajęcia nr V
Wyzwalacze
Zdarzeniowo (podczas zajścia operacji na
bazie danych) uruchamiane nazwane bloki PL/SQL, związane z tabelą, widokiem,
schematem lub bazą danych.
Wyzwalacze
Automatycznie uruchamiane w trakcie takich zdarzeń jak:
• każdego wiersza związanego z instrukcja DML (z wykorzystaniem FOR EACH ROW)
• użycia instrukcji DML (INSERT, DELETE, UPDATE)
• użycia instrukcji DDL (CREATE, DROP, ALTER)
• zdarzenia bazodanowego (SERVERERROR(AFTER), LOGON(AFTER), LOGOFF(BEFORE),
STARTUP(AFTER)(DATABASE),
SHUTDOWN(BEFORE) (DATABASE))
Składnia wyzwalaczy
* - może być kilka rozdzielonych słowem ‘OR’
• INSERT, DELETE -- wyzwalacz dla instrukcji odp. INSERT lub DELETE
• UPDATE [OF kol,...] -- wyzwalacz dla instrukcji UPDATE (modyfikującej kolumnę)
• Klauzula INSTEAD OF – jest związana z implementacją wyzwalacza na widoku, który może być oparty o złączenia tabel i wówczas zamiast próby operacji na widoku (co się nie powiedzie) wykonywany jest kod wyzwalacza
• Klauzula WHEN z warunkiem (występującą z FOR EACH ROW) powoduje uruchomienie wyzwalacza dla wiersza jeśli jest spełniony warunek.
• Przed deklaracją zmiennych bloku występuje DECLARE a nie występuje IS
CREATE [OR REPLACE] TRIGGER nazwa_trg
[BEFORE|AFTER] [INSTEAD OF] [rodzaj instrukcji]*
ON [tabela|widok|DATABASE|SCHEMA]!
[FOR EACH ROW ]
[WHEN ( warunek logiczny )]
blok PL/SQL
Wyzwalacze – zmienne wiersza
W wyzwalaczu wierszowym można odwoływać się do starych i nowych wartości w wierszu:
• :OLD.kolumna - kolumna przed zmiana
• :NEW.kolumna - kolumna po zmianie
(w warunku WHEN nie występują dwukropki)
w wyzwalaczu dla INSERT nie występują zmienne OLD w wyzwalaczu dla DELETE nie występują zmienne NEW
Wyzwalacze - stosowanie
Specjalne zmienne systemowe typu BOOLEAN informują o typie operacji:
• INSERTING - wyzwalacz uruchomiony dla INSERT
• DELETING - wyzwalacz uruchomiony dla DELETE
• UPDATING - wyzwalacz uruchomiony dla UPDATE
Wyzwalacze – włączanie/wyłączanie
Operacja na wyzwalaczach:
• ALTER TRIGGER nazwa_trg [ENABLE|DISABLE]!;
- włącz / wyłącz wyzwalacz
• DROP TRIGGER nazwa_trg; - usuń
• ALTER TRIGGER nazwa_trg COMPILE; - kompiluj kod wyzwalacza
Dane o wyzwalaczach użytkownika są przechowywane w perspektywie USER_TRIGGERS.
Wyzwalacze - przykłady
Automatyczne nadanie wartości dla kolumny klucza głównego
Zadanie 1: przetestować
CREATE OR REPLACE TRIGGER new_dept_rl_i BEFORE INSERT ON NEW_DEPT
FOR EACH ROW BEGIN
SELECT NVL(MAX(deptno),0)+10 INTO :NEW.deptno
FROM NEW_DEPT;
END;
Wyzwalacze - przykłady
CREATE OR REPLACE TRIGGER new_dept_u BEFORE UPDATE OF loc ON NEW_DEPT
BEGIN
DBMS_OUTPUT.PUT_LINE( 'lokalizacja zmieniona' );
END;
CREATE OR REPLACE TRIGGER new_dept_rl_u BEFORE UPDATE OF loc ON NEW_DEPT
FOR EACH ROW BEGIN
DBMS_OUTPUT.PUT_LINE( 'lokalizacja zmieniona z '
|| :OLD.loc
|| ' na ' ||
:NEW.loc );
Wyzwalacze - przykłady
CREATE OR REPLACE TRIGGER new_dept_iu BEFORE UPDATE OR INSERT ON NEW_DEPT BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE( 'Wykonano insert');
ELSE
DBMS_OUTPUT.PUT_LINE( 'Wykonano update');
END IF;
END;
CREATE OR REPLACE TRIGGER new_dept_rl_iu BEFORE UPDATE OR INSERT ON NEW_DEPT
FOR EACH ROW BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE( 'Wykonano insert wiersza');
ELSE
DBMS_OUTPUT.PUT_LINE( 'Wykonano update wiersza');
END IF;
END;
Zadanie 3: przetestować
Wyzwalacze - przykłady
Zadanie 4:
1. Osadzić
CREATE OR REPLACE TRIGGER new_dept_d BEFORE DELETE ON NEW_DEPT
BEGIN
IF RTRIM(TO_CHAR(SYSDATE, 'DAY') )= 'SOBOTA' THEN DBMS_OUTPUT.PUT_LINE( 'ZMIANA W SOBOTĘ' );
ELSE
RAISE_APPLICATION_ERROR( -20111
, 'Można kasować tylko w sobotę');
END IF;
END;
Zadanie
Wykonać auditing usuwania dla tabeli new_dept Oprócz usuwanych danych rejestrować nazwę
użytkownika (user) i czas (sysdate)
Tabela audytu niech ma nazwę new_dept_aud Dodatowe, opisane wyżej kolumny, to
data_czas_operacji i uzytkownik
Dokonać kolejno operacji usunięcia wierszy – sprawdzić działanie audytu
