PL/SQL

PL/SQL(5) M. Rakowski - WSISiZ 1

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 OLDw 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_iBEFORE INSERT ON NEW_DEPTFOR EACH ROWBEGIN

SELECT NVL(MAX(deptno),0)+10INTO :NEW.deptnoFROM NEW_DEPT;

END;




CREATE OR REPLACE TRIGGER new_dept_uBEFORE UPDATE OF loc ON NEW_DEPTBEGINDBMS_OUTPUT.PUT_LINE( 'lokalizacja zmieniona' );END;

CREATE OR REPLACE TRIGGER new_dept_rl_uBEFORE UPDATE OF loc ON NEW_DEPTFOR EACH ROWBEGIN DBMS_OUTPUT.PUT_LINE( 'lokalizacja zmieniona z ' || :OLD.loc

|| ' na ' ||:NEW.loc );

END new_dept_rl_u;


Wyzwalacze - przykładyCREATE OR REPLACE TRIGGER new_dept_iuBEFORE UPDATE OR INSERT ON NEW_DEPTBEGIN

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_iuBEFORE UPDATE OR INSERT ON NEW_DEPTFOR EACH ROWBEGIN

IF INSERTING THEN DBMS_OUTPUT.PUT_LINE( 'Wykonano insert wiersza'); ELSE DBMS_OUTPUT.PUT_LINE( 'Wykonano update wiersza');END IF;

END;




Zadanie 4: 1. Osadzić2. Dokonać usunięcia rekordu z zadania 13. Zmienić dzień w IF oraz w opisie błędu na ‘WTOREK’4. Dokonać próby usunięcia dowolnego rekordu z NEW_DEPT

CREATE OR REPLACE TRIGGER new_dept_dBEFORE DELETE ON NEW_DEPTBEGIN

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_deptOprócz usuwanych danych rejestrować nazwę

użytkownika (user) i czas (sysdate)Tabela audytu niech ma nazwę new_dept_audDodatowe, opisane wyżej kolumny, to

data_czas_operacji i uzytkownikDokonać kolejno operacji usunięcia wierszy –

sprawdzić działanie audytu

PL/SQL

Documents

Transcript of PL/SQL