Download
1 / 12
120 likes | 282 Views
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:
E N D
PL/SQL Zajęcia nr V M. Rakowski - WSISiZ
Wyzwalacze Zdarzeniowo (podczas zajścia operacji na bazie danych) uruchamiane nazwane bloki PL/SQL, związane z tabelą, widokiem, schematem lub bazą danych. M. Rakowski - WSISiZ
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)) M. Rakowski - WSISiZ
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 M. Rakowski - WSISiZ
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 M. Rakowski - WSISiZ
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 M. Rakowski - WSISiZ
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. M. Rakowski - WSISiZ
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; M. Rakowski - WSISiZ
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; Zadanie 2: przetestować 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; M. Rakowski - WSISiZ
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; Zadanie 3: przetestować 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; M. Rakowski - WSISiZ
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: • Osadzić • Dokonać usunięcia rekordu z zadania 1 • Zmienić dzień w IF oraz w opisie błędu na ‘WTOREK’ • Dokonać próby usunięcia dowolnego rekordu z NEW_DEPT M. Rakowski - WSISiZ
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 M. Rakowski - WSISiZ
