Oracle Samouczek wyzwalania PL/SQL: Zamiast, związek [Przykład]
Co to jest wyzwalacz w PL/SQL?
WYZWALACZE są przechowywanymi programami, które są uruchamiane przez Oracle silnik automatycznie, gdy instrukcje DML, takie jak wstawianie, aktualizacja, usuwanie, zostaną wykonane w tabeli lub wystąpią pewne zdarzenia. Kod, który ma zostać wykonany w przypadku wyzwalacza, można zdefiniować zgodnie z wymaganiami. Możesz wybrać zdarzenie, po którym ma zostać uruchomiony wyzwalacz, oraz czas wykonania. Celem wyzwalacza jest utrzymanie integralności informacji w bazie danych.
Korzyści z wyzwalaczy
Oto zalety wyzwalaczy.
- Automatyczne generowanie niektórych wartości kolumn pochodnych
- Wymuszanie integralności referencyjnej
- Rejestrowanie zdarzeń i przechowywanie informacji o dostępie do tabeli
- Audyt
- Syncstraszliwa replikacja tabel
- Nakładanie uprawnień bezpieczeństwa
- Zapobieganie nieważnym transakcjom
Rodzaje wyzwalaczy w Oracle
Wyzwalacze można klasyfikować na podstawie następujących parametrów.
- Klasyfikacja na podstawie wyczucie czasu
- PRZED wyzwalaczem: Uruchamia się przed wystąpieniem określonego zdarzenia.
- AFTER Trigger: Uruchamia się po wystąpieniu określonego zdarzenia.
- ZAMIAST Wyzwalacza: Specjalny typ. Dowiesz się więcej na temat dalszych tematów. (tylko dla DML)
- Klasyfikacja na podstawie poziom
- Wyzwalacz na poziomie STATEMENT: Uruchamia się jeden raz dla określonej instrukcji zdarzenia.
- Wyzwalacz na poziomie ROW: uruchamia się dla każdego rekordu, którego dotyczy określone zdarzenie. (tylko dla DML)
- Klasyfikacja na podstawie wydarzenie
- Wyzwalacz DML: Uruchamia się po określeniu zdarzenia DML (INSERT/UPDATE/DELETE)
- Wyzwalacz DDL: Uruchamia się, gdy określone zostanie zdarzenie DDL (CREATE/ALTER)
- Wyzwalacz DATABASE: Uruchamia się, gdy określone zostanie zdarzenie w bazie danych (LOGON/LOGOFF/STARTUP/SHUTDOWN)
Zatem każdy wyzwalacz jest kombinacją powyższych parametrów.
Jak utworzyć wyzwalacz
Poniżej znajduje się składnia tworzenia wyzwalacza.
CREATE [ OR REPLACE ] TRIGGER <trigger_name> [BEFORE | AFTER | INSTEAD OF ] [INSERT | UPDATE | DELETE......] ON<name of underlying object> [FOR EACH ROW] [WHEN<condition for trigger to get execute> ] DECLARE <Declaration part> BEGIN <Execution part> EXCEPTION <Exception handling part> END;
Wyjaśnienie składni:
- Powyższa składnia przedstawia różne opcjonalne instrukcje, które są obecne podczas tworzenia wyzwalacza.
- BEFORE/AFTER określi czas wydarzenia.
- WSTAW/AKTUALIZUJ/LOGUJ/UTWÓRZ/itp. określi zdarzenie, dla którego należy uruchomić wyzwalacz.
- Klauzula ON określi, na którym obiekcie obowiązuje powyższe zdarzenie. Przykładowo będzie to nazwa tabeli na której może wystąpić zdarzenie DML w przypadku DML Trigger.
- Polecenie „FOR EACH ROW” określi wyzwalacz poziomu WIERSZA.
- Klauzula WHEN określi dodatkowy warunek, w którym wyzwalacz musi zostać uruchomiony.
- Część deklaracyjna, część wykonawcza i część obsługi wyjątków są takie same jak pozostałe Bloki PL/SQL. Część deklaracji i część obsługi wyjątków są opcjonalne.
:NOWY i :STARY Klauzula
W przypadku wyzwalacza na poziomie wiersza wyzwalacz jest uruchamiany dla każdego powiązanego wiersza. Czasami wymagana jest znajomość wartości przed i po instrukcji DML.
Oracle udostępnił dwie klauzule w wyzwalaczu na poziomie RECORD do przechowywania tych wartości. Możemy użyć tych klauzul, aby odnieść się do starych i nowych wartości wewnątrz treści wyzwalacza.
- :NEW – Przechowuje nową wartość dla kolumn tabeli/widoku bazowego podczas wykonywania wyzwalacza
- :OLD – Przechowuje starą wartość kolumn tabeli/widoku bazowego podczas wykonywania wyzwalacza
Klauzuli tej należy używać w oparciu o zdarzenie DML. Poniższa tabela określi, która klauzula jest ważna dla której instrukcji DML (INSERT/UPDATE/DELETE).
INSERT | Aktualizacja | DELETE | |
---|---|---|---|
:NOWY | WAŻNY | WAŻNY | NIEWAŻNY. W przypadku usunięcia nie ma nowej wartości. |
:STARY | NIEWAŻNY. W przypadku wkładek nie ma starej wartości | WAŻNY | WAŻNY |
ZAMIAST wyzwalacza
„INSTEAD OF trigger” to specjalny typ wyzwalacza. Jest używany tylko w wyzwalaczach DML. Jest używany, gdy jakiekolwiek zdarzenie DML ma wystąpić w widoku złożonym.
Rozważmy przykład, w którym widok jest tworzony z 3 tabel podstawowych. Jeśli w tym widoku zostanie wywołane jakiekolwiek zdarzenie DML, stanie się ono nieważne, ponieważ dane zostaną pobrane z 3 różnych tabel. Zatem w tym przypadku używany jest wyzwalacz ZAMIAST. Wyzwalacz INSTEAD OF służy do bezpośredniej modyfikacji tabel podstawowych zamiast modyfikowania widoku dla danego zdarzenia.
1 przykład:W tym przykładzie utworzymy złożony widok z dwóch tabel bazowych.
- Tabela_1 to pusta tabela i
- Tabela_2 to tabela działów.
Następnie zobaczymy, jak wyzwalacz INSTEAD OF jest używany do wydawania polecenia UPDATE szczegółów lokalizacji w tym złożonym widoku. Zobaczymy również, jak :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 utworzonej powyżej tabeli
- Krok 4: Aktualizacja widoku przed wyzwalaczem zamiast
- Krok 5: Utworzenie wyzwalacza zamiast wyzwalacza
- Krok 6: Aktualizacja widoku po wyzwoleniu zamiast
Krok 1) Tworzenie 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 tabeli „dept”.
Wydajność
Tabela utworzona
Krok 2) Teraz, ponieważ utworzyliśmy tabelę, wypełnimy ją przykładowymi wartościami i utworzeniem widoków dla powyższych tabel.
BEGIN INSERT INTO DEPT VALUES(10,‘HR’,‘USA’); INSERT INTO DEPT VALUES(20,'SALES','UK’); INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); COMMIT; END; / BEGIN INSERT 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 „dept”.
- Linia kodu 20-26: Wstawianie danych do tabeli 'emp'.
Wydajność
Procedura PL/SQL zakończony
Krok 3) Tworzenie widoku dla utworzonej powyżej tabeli.
CREATE VIEW guru99_emp_view( Employee_name:dept_name,location) AS SELECT emp.emp_name,dept.dept_name,dept.location FROM emp,dept WHERE emp.dept_no=dept.dept_no; /
SELECT * FROM guru99_emp_view;
Objaśnienie kodu
- Linia kodu 27-32: Utworzenie widoku „guru99_emp_view”.
- Linia kodu 33: Zapytanie o guru99_emp_view.
Wydajność
Widok utworzony
IMIĘ I NAZWISKO PRACOWNIKA | DEPT_NAME | LOKALIZACJA |
---|---|---|
ZZZ | HR | USA |
YYY | OBROTY | UK |
XXX | FINANSOWA | JAPONIA |
Krok 4) Aktualizacja widoku przed wyzwalaczem.
BEGIN UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’; COMMIT; END; /
Objaśnienie kodu
- Linia kodu 34-38: Zaktualizuj lokalizację „XXX” na „FRANCE”. Zgłosił wyjątek, ponieważ instrukcje DML nie są dozwolone w widoku złożonym.
Wydajność
ORA-01779: nie można zmodyfikować kolumny mapowanej na tabelę niezabezpieczoną kluczem
ORA-06512: w linii 2
Krok 5)Aby uniknąć błędów podczas aktualizacji widoku w poprzednim kroku, w tym kroku użyjemy „zamiast wyzwalacza”.
CREATE TRIGGER guru99_view_modify_trg INSTEAD OF UPDATE ON guru99_emp_view FOR EACH ROW BEGIN UPDATE dept SET location=:new.location WHERE 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 ROW. Zawiera instrukcję aktualizacji służącą do aktualizacji lokalizacji w tabeli podstawowej „dept”.
- Linia kodu 44: Instrukcja aktualizacji używa „:NEW” i „: OLD”, aby znaleźć wartości kolumn przed i po aktualizacji.
Wydajność
Wyzwalacz został utworzony
Krok 6) Aktualizacja widoku po wyzwalaczu „instead-of”. Teraz błąd nie pojawi się, ponieważ „instead of trigger” obsłuży operację aktualizacji tego złożonego widoku. A gdy kod zostanie wykonany, lokalizacja pracownika XXX zostanie zaktualizowana z „Japonii” na „Francję”.
BEGIN UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; COMMIT; END; /
SELECT * FROM guru99_emp_view;
Wyjaśnienie kodu:
- Linia kodu 49-53: Aktualizacja lokalizacji „XXX” na „FRANCJA”. Powiodło się, ponieważ wyzwalacz „INSTEAD OF” zatrzymał faktyczną instrukcję aktualizacji w widoku i przeprowadził aktualizację tabeli podstawowej.
- Linia kodu 55: Weryfikacja zaktualizowanego rekordu.
Wyjście:
Procedura PL/SQL została pomyślnie ukończona
IMIĘ I NAZWISKO PRACOWNIKA | DEPT_NAME | LOKALIZACJA |
---|---|---|
ZZZ | HR | USA |
YYY | OBROTY | UK |
XXX | FINANSOWA | FRANCJA |
Wyzwalacz złożony
Wyzwalacz złożony to wyzwalacz umożliwiający określenie akcji dla każdego z czterech punktów czasowych w treści pojedynczego wyzwalacza. Cztery różne obsługiwane punkty czasowe przedstawiono poniżej.
- PRZED OŚWIADCZENIEM – poziom
- PRZED RZĄDEM – poziom
- PO WIERSZU – poziom
- PO OŚWIADCZENIU – poziom
Zapewnia możliwość łączenia działań dla różnych momentów w tym samym wyzwalaczu.
CREATE [ OR REPLACE ] TRIGGER <trigger_name> FOR [INSERT | UPDATE | DELET.......] ON <name of underlying object> <Declarative part> BEFORE STATEMENT IS BEGIN <Execution part>; END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN <Execution part>; END EACH ROW; AFTER EACH ROW IS BEGIN <Execution part>; END AFTER EACH ROW; AFTER STATEMENT IS BEGIN <Execution part>; END AFTER STATEMENT; END;
Wyjaśnienie składni:
- Powyższa składnia pokazuje tworzenie wyzwalacza „COMPOUND”.
- Sekcja deklaratywna jest wspólna dla wszystkich bloków wykonawczych w treści wyzwalacza.
- Te 4 bloki czasowe mogą występować w dowolnej kolejności. Posiadanie wszystkich tych 4 bloków czasowych nie jest obowiązkowe. Możemy utworzyć wyzwalacz COMPOUND tylko dla wymaganych czasów.
1 przykład: W tym przykładzie utworzymy wyzwalacz, który automatycznie wypełni kolumnę wynagrodzeń wartością domyślną 5000.
CREATE TRIGGER emp_trig FOR INSERT ON emp COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN :new.salary:=5000; END BEFORE EACH ROW; END emp_trig; /
BEGIN INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); COMMIT; END; /
SELECT * FROM emp WHERE emp_no=1004;
Wyjaśnienie kodu:
- Linia kodu 2-10: Tworzenie wyzwalacza złożonego. Jest on tworzony dla poziomu BEFORE ROW w celu wypełnienia wynagrodzenia 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: Sprawdzanie wstawionego rekordu.
Wyjście:
Wyzwalacz został utworzony
Procedura PL/SQL została pomyślnie ukończona.
EMP_NAME | EMP_NO | WYNAGRODZENIE | MANAGER | 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 <trigger_name> [ENABLE|DISABLE]; ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;
Wyjaś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 także różne klasyfikacje i omówiliśmy wyzwalacz ZAMIAST i wyzwalacz ZŁOŻONY.