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.

Utwórz wyzwalacz

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

ZAMIAST wyzwalacza

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.

ZAMIAST wyzwalacza

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.

ZAMIAST wyzwalacza

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.

ZAMIAST wyzwalacza

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”.

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ę”.

ZAMIAST wyzwalacza

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.

Wyzwalacz złożony

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.

Wyzwalacz złożony

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.