Samouczek dotyczący wyzwalacza Oracle PL / SQL: zamiast, złożony (przykład)

Spisie treści:

Anonim

Co to jest Trigger w PL / SQL?

TRIGGERS to zapisane programy, które są uruchamiane automatycznie przez silnik Oracle, gdy instrukcje DML, takie jak wstawianie, aktualizacja, usuwanie, są wykonywane na tabeli lub gdy wystąpią jakieś zdarzenia. Kod, który ma zostać wykonany w przypadku wyzwolenia, można zdefiniować zgodnie z wymaganiami. Możesz wybrać zdarzenie, przy którym wyzwalacz ma zostać uruchomiony, oraz czas wykonania. Celem wyzwalacza jest zachowanie integralności informacji w bazie danych.

W tym samouczku nauczysz się:

  • Korzyści z wyzwalaczy
  • Typy wyzwalaczy w Oracle
  • Jak stworzyć wyzwalacz
  • : NOWY i: STARY Klauzula
  • ZAMIAST wyzwalacza
  • Wyzwalacz złożony

Korzyści z wyzwalaczy

Oto zalety wyzwalaczy.

  • Automatyczne generowanie niektórych wartości kolumn pochodnych
  • Wymuszanie więzów integralności
  • Rejestrowanie zdarzeń i przechowywanie informacji o dostępie do tabeli
  • Audyt
  • Synchroniczna replikacja tabel
  • Nakładanie uprawnień bezpieczeństwa
  • Zapobieganie nieprawidłowym transakcjom

Typy wyzwalaczy w Oracle

Wyzwalacze można sklasyfikować na podstawie następujących parametrów.

  • Klasyfikacja oparta na czasie
    • PRZED wyzwoleniem: jest uruchamiany przed wystąpieniem określonego zdarzenia.
    • AFTER Trigger: jest uruchamiany po wystąpieniu określonego zdarzenia.
    • ZAMIAST wyzwalacza: Specjalny typ. Dowiesz się więcej na dalsze tematy. (tylko dla DML)
  • Klasyfikacja na podstawie poziomu
    • Wyzwalacz poziomu STATEMENT: jest uruchamiany jeden raz dla określonej instrukcji zdarzenia.
    • Wyzwalacz poziomu WIERSZA: Odpala dla każdego rekordu, na który wpłynęło określone zdarzenie. (tylko dla DML)
  • Klasyfikacja na podstawie zdarzenia
    • Wyzwalacz DML: Odpala, gdy określone jest zdarzenie DML (WSTAW / AKTUALIZUJ / USUŃ)
    • Wyzwalacz DDL: jest uruchamiany, gdy określono zdarzenie DDL (UTWÓRZ / ZMIENIJ)
    • Wyzwalacz bazy danych: Odpala, gdy określone jest zdarzenie bazy danych (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Zatem każdy wyzwalacz jest połączeniem powyższych parametrów.

Jak stworzyć wyzwalacz

Poniżej znajduje się składnia tworzenia wyzwalacza.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Objaśnienie składni:

  • Powyższa składnia przedstawia różne opcjonalne instrukcje, które są obecne podczas tworzenia wyzwalacza.
  • PRZED / PO określa czas zdarzenia.
  • INSERT / UPDATE / LOGON / CREATE / etc. określi zdarzenie, dla którego wyzwalacz musi zostać uruchomiony.
  • Klauzula ON określi, na jakim obiekcie powyższe zdarzenie obowiązuje. Na przykład będzie to nazwa tabeli, w której zdarzenie DML może wystąpić w przypadku wyzwalacza DML.
  • Polecenie „FOR EACH ROW” określa wyzwalanie poziomu ROW.
  • Klauzula WHEN określa dodatkowy warunek, w którym wyzwalacz musi zostać uruchomiony.
  • Część deklaracji, część wykonawcza, część obsługi wyjątków jest taka sama, jak w przypadku innych bloków PL / SQL. Część deklaracji i część dotycząca obsługi wyjątków są opcjonalne.

: NOWY i: STARY Klauzula

W wyzwalaczu na poziomie wiersza wyzwalacz jest uruchamiany dla każdego powiązanego wiersza. Czasami wymagana jest znajomość wartości przed i po instrukcji DML.

Firma Oracle dostarczyła dwie klauzule w wyzwalaczu poziomu RECORD, aby zachować te wartości. Możemy użyć tych klauzul, aby odwołać się do starych i nowych wartości w treści wyzwalacza.

  • : NEW - Przechowuje nową wartość dla kolumn tabeli podstawowej / widoku podczas wykonywania wyzwalacza
  • : OLD - Przechowuje starą wartość kolumn tabeli bazowej / widoku podczas wykonywania wyzwalacza

Ta klauzula powinna być używana na podstawie zdarzenia DML. Poniższa tabela określa, która klauzula obowiązuje dla której instrukcji DML (INSERT / UPDATE / DELETE).

WSTAWIĆ AKTUALIZACJA KASOWAĆ
:NOWY WAŻNY WAŻNY NIEWAŻNY. Nie ma nowej wartości w przypadku usunięcia.
:STARY NIEWAŻNY. W przypadku wkładek nie ma starej wartości WAŻNY WAŻNY

ZAMIAST wyzwalacza

„ZAMIAST wyzwalacza” to specjalny typ wyzwalacza. Jest używany tylko w wyzwalaczach DML. Jest używany, gdy jakiekolwiek zdarzenie DML ma wystąpić w złożonym widoku.

Rozważmy przykład, w którym widok jest tworzony z 3 tabel podstawowych. Gdy w tym widoku zostanie wydane jakiekolwiek zdarzenie DML, stanie się ono nieważne, ponieważ dane są pobierane z 3 różnych tabel. Więc w tym INSTEAD OF jest używany wyzwalacz. Wyzwalacz INSTEAD OF służy do bezpośredniej modyfikacji tabel bazowych zamiast modyfikowania widoku dla danego zdarzenia.

Przykład 1 : W tym przykładzie utworzymy złożony widok z dwóch tabel bazowych.

  • Table_1 to tabela emp, a
  • Tabela_2 to tabela działu.

Następnie zobaczymy, jak wyzwalacz INSTEAD OF jest używany do wydawania instrukcji UPDATE szczegółowej lokalizacji w tym złożonym widoku. Zobaczymy również, w jaki sposób: NEW i: OLD są przydatne w wyzwalaczach.

  • Krok 1: Utworzenie tabeli „emp” i „dept” z odpowiednimi kolumnami
  • Krok 2: Wypełnianie tabeli przykładowymi wartościami
  • Krok 3: Tworzenie widoku dla powyższej utworzonej tabeli
  • Krok 4: Aktualizacja widoku przed wyzwalaczem zamiast
  • Krok 5: Utworzenie wyzwalacza zamiast
  • Krok 6: Aktualizacja widoku po wyzwalaczu zamiast

Krok 1) Utworzenie tabeli „emp” i „dept” z odpowiednimi kolumnami

CREATE TABLE emp(emp_no NUMBER,emp_name VARCHAR2(50),salary NUMBER,manager VARCHAR2(50),dept_no NUMBER);/CREATE TABLE dept(Dept_no NUMBER,Dept_name VARCHAR2(50),LOCATION VARCHAR2(50));/

Objaśnienie kodu

  • Linia kodu 1-7 : Tworzenie tabeli „emp”.
  • Linia kodu 8-12 : Tworzenie „działu” tabeli.

Wynik

Utworzono tabelę

Krok 2) Teraz, odkąd utworzyliśmy tabelę, wypełnimy tę tabelę przykładowymi wartościami i utworzeniem widoków dla powyższych tabel. 

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10);COMMIT;END;/

Objaśnienie kodu

  • Linia kodu 13-19 : Wstawianie danych do tabeli „dział”.
  • Linia kodu 20-26: Wstawianie danych do tabeli „emp”.

Wynik

Procedura PL / SQL zakończona

Krok 3) Stworzenie widoku dla powyższej utworzonej tabeli. 

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE emp.dept_no=dept.dept_no;/
SELECT * FROM guru99_emp_view;

Objaśnienie kodu

  • Linia kodu 27-32: Tworzenie widoku „guru99_emp_view”.
  • Linia kodu 33: Zapytanie do guru99_emp_view.

Wynik

Widok utworzony

IMIĘ I NAZWISKO PRACOWNIKA DEPT_NAME LOKALIZACJA
ZZZ HR USA
YYY SPRZEDAŻ UK
XXX BUDŻETOWY JAPONIA

Krok 4) Aktualizacja widoku przed zamiast wyzwalacza.

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;COMMIT;END;/

Objaśnienie kodu

  • Linia kodowa 34–38: Zaktualizuj lokalizację „XXX” na „FRANCJA”. Podniósł wyjątek, ponieważ instrukcje DML nie są dozwolone w złożonym widoku.

Wynik

ORA-01779: nie można modyfikować kolumny, która jest odwzorowywana na niezachowaną tabelę

ORA-06512: w linii 2

Krok 5) Aby uniknąć błędu napotkanego podczas aktualizowania widoku w poprzednim kroku, w tym kroku użyjemy „zamiast wyzwalacza”.

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

Objaśnienie kodu

  • Linia kodu 39: Utworzenie wyzwalacza INSTEAD OF dla zdarzenia „UPDATE” w widoku „guru99_emp_view” na poziomie WIERSZ. Zawiera instrukcję aktualizacji, aby zaktualizować lokalizację w tabeli podstawowej „dział”.
  • Linia kodu 44: Instrukcja aktualizacji używa znaków „: NEW” i „: OLD” do znalezienia wartości kolumn przed i po aktualizacji.

Wynik

Wyzwalacz został utworzony

Krok 6) Aktualizacja widoku po wyzwalaczu zamiast. Teraz błąd nie pojawi się, ponieważ „zamiast wyzwalacza” będzie obsługiwać operację aktualizacji tego złożonego widoku. Po wykonaniu kodu lokalizacja pracownika XXX zostanie zaktualizowana do „Francja” z „Japonii”.

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

Objaśnienie kodu:

  • Linia kodowa 49–53: Aktualizacja lokalizacji „XXX” na „FRANCJA”. Operacja powiodła się, ponieważ wyzwalacz „INSTEAD OF” zatrzymał wyświetlaną instrukcję aktualizacji i wykonał aktualizację tabeli podstawowej.
  • Linia kodu 55: weryfikacja zaktualizowanego rekordu.

Wynik:

Procedura PL / SQL zakończona pomyślnie

IMIĘ I NAZWISKO PRACOWNIKA DEPT_NAME LOKALIZACJA
ZZZ HR USA
YYY SPRZEDAŻ UK
XXX BUDŻETOWY FRANCJA

Wyzwalacz złożony

Wyzwalacz złożony jest wyzwalaczem, który umożliwia określenie działań dla każdego z czterech punktów czasowych w pojedynczej treści wyzwalacza. Cztery różne punkty czasowe, które obsługuje, są jak poniżej.

  • PRZED OŚWIADCZENIEM - poziom
  • PRZED RZĘDEM - poziom
  • PO RZĘDZIE - poziom
  • PO OŚWIADCZENIU - poziom

Zapewnia możliwość łączenia działań dla różnych czasów w tym samym wyzwalaczu.

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;END AFTER STATEMENT;END;

Objaśnienie składni:

  • Powyższa składnia ilustruje tworzenie wyzwalacza „COMPOUND”.
  • Sekcja deklaratywna jest wspólna dla całego bloku wykonywania w treści wyzwalacza.
  • Te 4 bloki taktowania mogą mieć dowolną sekwencję. Posiadanie wszystkich tych 4 bloków czasowych nie jest obowiązkowe. Możemy utworzyć wyzwalacz COMPOUND tylko dla wymaganych czasów.

Przykład 1 : W tym przykładzie utworzymy regułę, która automatycznie wypełni kolumnę wynagrodzenia wartością domyślną 5000.

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30);COMMIT;END;/
SELECT * FROM emp WHERE emp_no=1004;

Objaśnienie kodu:

  • Linia kodu 2-10 : Tworzenie wyzwalacza złożonego. Jest on tworzony w celu określenia czasu PRZED WIERSZEM, aby wypełnić wynagrodzenie wartością domyślną 5000. Spowoduje to zmianę wynagrodzenia na wartość domyślną „5000” przed wstawieniem rekordu do tabeli.
  • Linia kodu 11-14 : Wstaw rekord do tabeli „emp”.
  • Linia kodu 16 : weryfikacja wstawionego rekordu.

Wynik:

Utworzono regułę

Procedura PL / SQL zakończona pomyślnie.

EMP_NAME EMP_NO WYNAGRODZENIE MENEDŻER DEPT_NO
CCC 1004 5000 AAA 30

Włączanie i wyłączanie wyzwalaczy

Wyzwalacze można włączyć lub wyłączyć. Aby włączyć lub wyłączyć wyzwalacz, należy podać instrukcję ALTER (DDL) dla wyzwalacza, który go wyłącza lub włącza.

Poniżej znajduje się składnia włączania / wyłączania wyzwalaczy.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Objaśnienie składni:

  • Pierwsza składnia pokazuje, jak włączyć / wyłączyć pojedynczy wyzwalacz.
  • Druga instrukcja pokazuje, jak włączyć / wyłączyć wszystkie wyzwalacze w określonej tabeli.

Podsumowanie

W tym rozdziale poznaliśmy wyzwalacze PL / SQL i ich zalety. Poznaliśmy również różne klasyfikacje i omówiliśmy ZAMIAST wyzwalacza i wyzwalacza złożonego.