Oracle Wyzwalacz PL/SQL: zamiast typów złożonych

Co to jest wyzwalacz w PL/SQL?

WYZWALACZE są przechowywane PL / SQL programy uruchamiane przez Oracle silnik automatycznie, gdy instrukcje DML Funkcje takie jak wstawianie, aktualizowanie i usuwanie są wykonywane na tabeli lub w momencie wystąpienia określonych zdarzeń. Kod, który ma zostać wykonany w przypadku wyzwalacza, można zdefiniować zgodnie z wymaganiami. Można wybrać zdarzenie, po którym wyzwalacz ma zostać uruchomiony, oraz czas wykonania. Celem wyzwalacza jest zachowanie 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 oparta na czasie

• PRZED wyzwalaczem: Wyzwala się przed wystąpieniem określonego zdarzenia.
• PO WYZWALENIU: Uruchamia się po wystąpieniu określonego zdarzenia.
• ZAMIAST Triggera: Specjalny typ. Więcej dowiesz się w kolejnych tematach. (tylko dla DML)

Klasyfikacja oparta na poziomie

• Poziom WYZWALACZA: Wywołuje się jeden raz dla określonego polecenia zdarzenia.
• Poziom ROW Wyzwalacz: Wywołuje się dla każdego rekordu, który został dotknięty określonym zdarzeniem. (tylko dla DML)

Klasyfikacja na podstawie zdarzenia

• Wyzwalacz DML: Uruchamia się, gdy określone zostanie zdarzenie DML (INSERT/UPDATE/DELETE).
• Wyzwalacz DDL: Uruchamia się, gdy określone zostanie zdarzenie DDL (CREATE/ALTER).
• Wyzwalacz BAZY DANYCH: Uruchamia się, gdy określone zostanie zdarzenie bazy 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. Poniższy zrzut ekranu pokazuje składnię tworzenia wyzwalacza w Oracle.

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śla czas wydarzenia.
• WSTAW/AKTUALIZUJ/LOGUJ/UTWÓRZ/itp. określi zdarzenie, dla którego należy uruchomić wyzwalacz.
• Klauzula ON określa obiekt, dla którego powyższe zdarzenie jest ważne. 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śli wyzwalacz na poziomie WIERSZA.
• Klauzula WHEN określa dodatkowy warunek, w którym wyzwalacz musi zostać uruchomiony.
• Część deklaracji, część wykonania i część obsługi wyjątków są takie same jak w pozostałych Bloki PL/SQLCzęść deklaracyjna i Obsługa wyjątków części 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 W wyzwalaczu na poziomie wiersza znajdują się dwie klauzule do przechowywania tych wartości. Możemy ich używać do odwoływania się do starych i nowych wartości w treści wyzwalacza.

• :NOWY – Przechowuje nową wartość dla kolumn tabeli/widoku bazowego podczas wykonywania wyzwalacza.
• :STARY – Przechowuje starą wartość kolumn tabeli/widoku bazowego podczas wykonywania wyzwalacza.

Ta klauzula powinna być używana w oparciu o zdarzenie DML. Poniższa tabela określa, która klauzula jest prawidłowa dla danego polecenia DML (INSERT/UPDATE/DELETE).

ZAMIAST wyzwalacza

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

Rozważmy przykład, w którym widok jest tworzony na podstawie trzech tabel bazowych. Po wywołaniu dowolnego zdarzenia DML w tym widoku, stanie się on nieważny, ponieważ dane pochodzą z trzech różnych tabel. Dlatego w tym przypadku używany jest wyzwalacz INSTEAD OF. Wyzwalacz INSTEAD OF służy do bezpośredniej modyfikacji tabel bazowych, a nie do modyfikacji widoku dla danego zdarzenia.

1 przykład: W tym przykładzie utworzymy złożony widok z dwóch tabel bazowych, gdzie Table_1 to tabela pracow, a Table_2 to tabela dział.

Następnie zobaczymy, jak wyzwalacz INSTEAD OF służy do wygenerowania komunikatu UPDATE szczegółów lokalizacji w tym złożonym widoku. Zobaczymy również, jak :NEW i :OLD są przydatne w wyzwalaczach. Przykład wygląda następująco:

• Krok 1: Tworzenie tabel „emp” i „dept” z odpowiednimi kolumnami
• Krok 2: Wypełnianie tabel wartościami przykładowymi
• Krok 3: Tworzenie widoku dla utworzonych powyżej tabel
• Krok 4: Aktualizacja widoku przed wyzwalaczem INSTEAD OF
• Krok 5: Tworzenie wyzwalacza INSTEAD OF
• Krok 6: Aktualizacja widoku po wyzwalaczu INSTEAD OF

Krok 1) Tworzenie tabel „emp” i „dept” z odpowiednimi kolumnami.

Poniższy zrzut ekranu przedstawia tworzenie tabel bazowych „emp” i „dept” w Oracle.

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));
/

Code Wyjaśnienie

• Code wiersz 1-7: Tworzenie tabeli 'emp'.
• Code wiersz 8-12: Utworzenie tabeli 'dept'.

Wyjście:

Table Created

Krok 2) Teraz, gdy utworzyliśmy tabele, wypełnimy je przykładowymi wartościami.

Poniższy zrzut ekranu pokazuje przykładowe wiersze wstawiane do tabel „dept” i „emp”.

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Wyjaśnienie

• Code wiersz 13-19: Wprowadzanie danych do tabeli „dept”.
• Code wiersz 20-26: Wprowadzanie danych do tabeli „emp”.

Wyjście:

PL/SQL procedure completed

Krok 3) Tworzenie widoku dla powyższych tabel.

Zrzut ekranu poniżej przedstawia tworzenie i wykonywanie zapytania w złożonym widoku.

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;

Code Wyjaśnienie

• Code wiersz 27-32: Utworzenie widoku „guru99_emp_view”.
• Code linia 33: Zapytanie o guru99_emp_view.

Wyjście:

View created

Krok 4) Aktualizacja widoku przed wyzwalaczem INSTEAD OF.

Poniższy zrzut ekranu pokazuje próbę aktualizacji widoku złożonego i wynikający z niej błąd.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code Wyjaśnienie

• Code wiersz 34-38: Zaktualizowano lokalizację „XXX” na „FRANCE”. Wystąpił wyjątek, ponieważ instrukcje DML nie są dozwolone bezpośrednio w widoku złożonym.

Wyjście:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

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

Poniższy zrzut ekranu przedstawia tworzenie wyzwalacza INSTEAD OF.

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;
/

Code Wyjaśnienie

• Code linia 39: Utworzenie wyzwalacza INSTEAD OF dla zdarzenia „UPDATE” w widoku „guru99_emp_view” na poziomie wiersza. Zawiera on instrukcję aktualizacji, która aktualizuje lokalizację w tabeli bazowej „dept”.
• Code linia 44: Polecenie aktualizacji używa poleceń „:NEW” i „:OLD”, aby znaleźć wartość kolumn przed i po aktualizacji.

Wyjście:

Trigger Created

Krok 6) Aktualizacja widoku po wyzwalaczu INSTEAD OF. Teraz błąd nie pojawi się, ponieważ wyzwalacz INSTEAD OF obsłuży operację aktualizacji tego złożonego widoku. Po wykonaniu kodu lokalizacja pracownika XXX zostanie zaktualizowana z „Japonii” na „Francję”.

Poniższy zrzut ekranu przedstawia pomyślną aktualizację za pomocą wyzwalacza INSTEAD OF i odświeżony widok.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code Wyjaśnienie:

• Code wiersz 49-53: Aktualizacja lokalizacji „XXX” na „FRANCE”. Operacja zakończyła się powodzeniem, ponieważ wyzwalacz „INSTEAD OF” zatrzymał faktyczne polecenie aktualizacji w widoku i wykonał aktualizację tabeli bazowej.
• Code linia 55: Weryfikacja zaktualizowanego rekordu.

Wyjście:

PL/SQL procedure successfully completed

Wyzwalacz złożony

Wyzwalacz złożony to wyzwalacz, który pozwala określić działania dla każdego z czterech punktów czasowych w jednym korpusie wyzwalacza. Cztery różne obsługiwane przez niego punkty czasowe przedstawiono poniżej.

• PRZED OŚWIADCZENIEM – poziom
• PRZED RZĄDEM – poziom
• PO WIERSZU – poziom
• PO OŚWIADCZENIU – poziom

Umożliwia łączenie działań o różnym czasie trwania w jednym wyzwalaczu.

Poniższy zrzut ekranu przedstawia składnię złożonego wyzwalacza z czterema sekcjami pomiaru czasu.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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 ilustruje tworzenie wyzwalacza „COMPOUND”.
• Sekcja deklaratywna jest wspólna dla wszystkich bloków wykonawczych w ciele wyzwalacza.
• Te cztery bloki czasowe mogą występować w dowolnej kolejności. Nie jest konieczne posiadanie wszystkich czterech bloków czasowych. Możemy utworzyć wyzwalacz ZŁOŻONY tylko dla wymaganych czasów.

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

Zrzut ekranu poniżej pokazuje przykład wyzwalacza złożonego i jego dane wyjściowe.

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;

Code Wyjaśnienie:

• Code wiersz 2-10: Utworzenie wyzwalacza złożonego. Jest on tworzony dla poziomu BEFORE ROW, aby wypełnić pensję domyślną wartością 5000. Spowoduje to zmianę pensji na domyślną wartość „5000” przed wstawieniem rekordu do tabeli.
• Code wiersz 11-14: Wstaw rekord do tabeli „emp”.
• Code linia 16: Weryfikacja wprowadzonego rekordu.

Wyjście:

Trigger created

PL/SQL procedure successfully completed.

Włączanie i wyłączanie wyzwalaczy

Wyzwalacze można włączać i wyłączać. 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 przedstawiono składnię 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.

FAQ