PL/SQL

1
PL/SQL

• Zajecia nr V

2
Wyzwalacze

• Zdarzeniowo (podczas zajscia operacji na bazie
  danych) uruchamiane nazwane bloki PL/SQL,
  zwiazane z tabela, widokiem, schematem lub baza
  danych.

3
Wyzwalacze

• Automatycznie uruchamiane w trakcie takich
  zdarzen jak
• kazdego wiersza zwiazanego z instrukcja DML (z
  wykorzystaniem FOR EACH ROW)
• uzycia instrukcji DML (INSERT, DELETE, UPDATE)
• uzycia instrukcji DDL (CREATE, DROP, ALTER)
• zdarzenia bazodanowego (SERVERERROR(AFTER),
  LOGON(AFTER), LOGOFF(BEFORE), STARTUP(AFTER)(DATAB
  ASE), SHUTDOWN(BEFORE) (DATABASE))

4
Skladnia wyzwalaczy
CREATE OR REPLACE TRIGGER nazwa_trg
BEFOREAFTER INSTEAD OF rodzaj
instrukcji ON tabelawidokDATABASESCHEMA!
FOR EACH ROW WHEN ( warunek logiczny )
blok PL/SQL

• - moze byc kilka rozdzielonych slowem OR
• INSERT, DELETE -- wyzwalacz dla instrukcji odp.
  INSERT lub DELETE
• UPDATE OF kol,... -- wyzwalacz dla instrukcji
  UPDATE (modyfikujacej kolumne)
• Klauzula INSTEAD OF jest zwiazana z
  implementacja wyzwalacza na widoku, który moze
  byc oparty o zlaczenia tabel i wówczas zamiast
  próby operacji na widoku (co sie nie powiedzie)
  wykonywany jest kod wyzwalacza
• Klauzula WHEN z warunkiem (wystepujaca z FOR EACH
  ROW) powoduje uruchomienie wyzwalacza dla wiersza
  jesli jest spelniony warunek.
• Przed deklaracja zmiennych bloku wystepuje
  DECLARE a nie wystepuje IS

5
Wyzwalacze zmienne wiersza

• W wyzwalaczu wierszowym mozna odwolywac sie do
  starych i nowych wartosci w wierszu
• OLD.kolumna - kolumna przed zmiana
• NEW.kolumna - kolumna po zmianie
• (w warunku WHEN nie wystepuja dwukropki)
• w wyzwalaczu dla INSERT nie wystepuja zmienne OLD
• w wyzwalaczu dla DELETE nie wystepuja zmienne NEW

6
Wyzwalacze - stosowanie

• Specjalne zmienne systemowe typu BOOLEAN
  informuja o typie operacji
• INSERTING - wyzwalacz uruchomiony dla INSERT
• DELETING - wyzwalacz uruchomiony dla DELETE
• UPDATING - wyzwalacz uruchomiony dla UPDATE

7
Wyzwalacze wlaczanie/wylaczanie

• Operacja na wyzwalaczach
• ALTER TRIGGER nazwa_trg ENABLEDISABLE! -
  wlacz / wylacz wyzwalacz
• DROP TRIGGER nazwa_trg - usun
• ALTER TRIGGER nazwa_trg COMPILE - kompiluj
  kod wyzwalacza
• Dane o wyzwalaczach uzytkownika sa przechowywane
  w perspektywie USER_TRIGGERS.

8
Wyzwalacze - przyklady
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 wartosci dla kolumny klucza
  glównego
• Zadanie 1 przetestowac

9
Wyzwalacze - przyklady
CREATE OR REPLACE TRIGGER new_dept_u BEFORE
UPDATE OF loc ON NEW_DEPT BEGIN DBMS_OUTPUT.PUT_LI
NE( '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 przetestowac

10
Wyzwalacze - przyklady
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 przetestowac

11
Wyzwalacze - przyklady
CREATE OR REPLACE TRIGGER new_dept_d BEFORE
DELETE ON NEW_DEPT BEGIN IF RTRIM(TO_CHAR(SYSDATE
, 'DAY') ) 'SOBOTA' THEN DBMS_OUTPUT.PUT_LIN
E( 'ZMIANA W SOBOTE' ) ELSE RAISE_APPLICATION_
ERROR( -20111 , 'Mozna kasowac tylko w
sobote') END IF END

• Zadanie 4
• Osadzic
• Dokonac usuniecia rekordu z zadania 1
• Zmienic dzien w IF oraz w opisie bledu na
  WTOREK
• Dokonac próby usuniecia dowolnego rekordu z
  NEW_DEPT

12
Zadanie

• Wykonac auditing usuwania dla tabeli new_dept
• Oprócz usuwanych danych rejestrowac nazwe
  uzytkownika (user) i czas (sysdate)
• Tabela audytu niech ma nazwe new_dept_aud
• Dodatowe, opisane wyzej kolumny, to
  data_czas_operacji i uzytkownik
• Dokonac kolejno operacji usuniecia wierszy
  sprawdzic dzialanie audytu