Transcript PL/SQL - korzen.org

PL/SQL
Zajęcia nr V
PL/SQL(5)
M. Rakowski - WSISiZ
1
Wyzwalacze
Zdarzeniowo (podczas zajścia operacji na
bazie danych) uruchamiane nazwane bloki
PL/SQL, związane z tabelą, widokiem,
schematem lub bazą danych.
PL/SQL(5)
M. Rakowski - WSISiZ
2
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))
PL/SQL(5)
M. Rakowski - WSISiZ
3
Składnia wyzwalaczy
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
* - 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
PL/SQL(5)
M. Rakowski - WSISiZ
4
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
PL/SQL(5)
M. Rakowski - WSISiZ
5
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
PL/SQL(5)
M. Rakowski - WSISiZ
6
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.
PL/SQL(5)
M. Rakowski - WSISiZ
7
Wyzwalacze - przykłady
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;
Automatyczne nadanie wartości dla kolumny
klucza głównego
Zadanie 1: przetestować
PL/SQL(5)
M. Rakowski - WSISiZ
8
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 );
END new_dept_rl_u;
Zadanie 2: przetestować
PL/SQL(5)
M. Rakowski - WSISiZ
9
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ć
PL/SQL(5)
M. Rakowski - WSISiZ
10
Wyzwalacze - przykłady
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 4:
1.
Osadzić
2.
Dokonać usunięcia rekordu z zadania 1
3.
Zmienić dzień w IF oraz w opisie błędu na ‘WTOREK’
4.
Dokonać próby usunięcia dowolnego rekordu z NEW_DEPT
PL/SQL(5)
M. Rakowski - WSISiZ
11
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
PL/SQL(5)
M. Rakowski - WSISiZ
12