Oracle PL / SQL Trigger Tutorial: zamiast, związek [przykład]

w tym samouczku dowiesz się –

• korzyści z wyzwalaczy
• rodzaje wyzwalaczy w Oracle
• Jak utworzyć Trigger
• : nowa i :stara Klauzula
• zamiast Trigger
• Compound Trigger

korzyści z wyzwalaczy

poniżej przedstawiono korzyści z wyzwalaczy.

• Automatyczne generowanie niektórych pochodnych wartości kolumn
• wymuszanie integralności odniesienia
• rejestrowanie zdarzeń i przechowywanie informacji o dostępie do tabeli
• audyt
• Synchroniczna replikacja tabel
• nakładanie autoryzacji zabezpieczeń
• zapobieganie nieprawidłowym transakcjom

typy wyzwalaczy w Oracle

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

• klasyfikacja oparta na czasie
  • przed wyzwalaniem: jest wywoływana przed wystąpieniem określonego zdarzenia.
  • AFTER Trigger: jest wywołana po wystąpieniu określonego zdarzenia.
  • zamiast Trigger: specjalny typ. Dowiesz się więcej o dalszych tematach. (tylko dla DML )
• klasyfikacja oparta na instrukcji level
  • WYZWALACZ poziomu: jest wywołany jeden raz dla określonej instrukcji event.
  • WYZWALACZ poziomu wiersza: jest wywoływany dla każdego rekordu, który został dotknięty w określonym zdarzeniu. (tylko dla DML)
• klasyfikacja oparta na zdarzeniu
  • WYZWALACZ DML: jest wywoływany, gdy określone jest Zdarzenie DML (INSERT/UPDATE/DELETE)
  • WYZWALACZ DDL: Jest wywołane, gdy określone jest Zdarzenie DDL (CREATE/ALTER)
  • WYZWALACZ bazy danych: jest wywołany, gdy określone jest Zdarzenie bazy danych (LOGON/LOGOFF/STARTUP/SHUTDOWN)

więc każdy wyzwalacz jest kombinacją powyższych parametrów.

Jak utworzyć Trigger

poniżej znajduje się składnia do tworzenia triggera.

CREATE TRIGGER <trigger_name> ON<name of underlying object> DECLARE<Declaration part>BEGIN<Execution part> EXCEPTION<Exception handling part> END;

Wyjaśnienie składni:

• powyższa składnia pokazuje różne opcjonalne instrukcje, które są obecne w tworzeniu wyzwalacza.
• BEFORE/ AFTER określi czas zdarzenia.
• INSERT / UPDATE / LOGON / CREATE / etc. określi Zdarzenie, dla którego wyzwalacz ma zostać uruchomiony.

klauzula

• ON określi, na którym obiekcie obowiązuje powyższe Zdarzenie. Na przykład będzie to nazwa tabeli, na której może wystąpić Zdarzenie DML w przypadku wyzwalacza DML.
• polecenie „dla każdego wiersza” określi WYZWALACZ poziomu wiersza.
• klauzula WHEN określi dodatkowy warunek, w którym WYZWALACZ musi wystrzelić.
• część deklaracji, część wykonania, część obsługi wyjątków jest taka sama jak w pozostałych blokach PL / SQL. Część deklaracji i część obsługi wyjątków są opcjonalne.

:NEW and :OLD Clause

w poziomie wiersza WYZWALACZ, wyzwalacz jest wywołany dla każdego powiązanego wiersza. I czasami jest wymagane, aby znać wartość przed i po instrukcji DML.

Oracle dostarczył dwie klauzule w wyzwalaczu poziomu rekordu, aby utrzymać te wartości. Możemy użyć tych klauzul, aby odnieść się do starych i nowych wartości wewnątrz ciała wyzwalacza.

• :NEW-przechowuje nową wartość kolumn tabeli / widoku bazowego podczas wykonywania wyzwalacza
• : OLD-przechowuje starą wartość kolumn tabeli/widoku bazowego podczas wykonywania wyzwalacza

ta klauzula powinna być użyta w oparciu o Zdarzenie DML. Poniższa tabela określi, która klauzula jest ważna dla której instrukcji DML (INSERT / UPDATE / DELETE).

zamiast wyzwalacza

„zamiast wyzwalacza” jest specjalnym typem wyzwalacza. Jest on używany tylko w wyzwalaczach DML. Jest on używany, gdy każde zdarzenie DML ma wystąpić w widoku złożonym.

rozważ przykład, w którym widok jest wykonany z 3 tabel bazowych. Gdy jakiekolwiek zdarzenie DML zostanie wydane w tym widoku, stanie się ono nieważne, ponieważ dane są pobierane z 3 różnych tabel. Więc w tym zamiast trigger jest używany. Zamiast wyzwalacza służy do bezpośredniej modyfikacji tabel bazowych, zamiast modyfikacji widoku dla danego zdarzenia.

przykład 1: w tym przykładzie stworzymy złożony widok z dwóch tabeli bazowej.

• Table_1 to tablica emp, a

• Table_2 to tablica Wydziałowa.

następnie zobaczymy, jak zamiast wyzwalacza jest używany do wydania aktualizacji instrukcji location detail w tym złożonym widoku. Zobaczymy również, jak :NEW i :OLD są przydatne w wyzwalaczach.

• Krok 1: Tworzenie 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 zamiast wyzwalacza
• Krok 5: Tworzenie zamiast wyzwalacza
• Krok 6: Aktualizacja widoku po zamiast wyzwalacza

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

Wyjaśnienie kodu

• linia kodu 1-7: Tworzenie tabeli „emp”.
• linia kodu 8-12: Tworzenie tabeli 'dept’.

Output

table Created

Krok 2) Teraz, gdy stworzyliśmy tabelę, wypełnimy ją przykładowymi wartościami i utworzymy widoki 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;/

Wyjaśnienie kodu

• linia kodu 13-19: wstawianie danych do tabeli 'dept’.
• linia kodu 20-26: wstawianie danych do tabeli ’emp’.

Wyjście

procedura PL/SQL zakończona

Krok 3) utworzenie 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;

Wyjaśnienie kodu

• linia kodu 27-32: utworzenie widoku 'guru99_emp_view’.

• linia kodu 33: Querying guru99_emp_view.

Output

View created

Step 4) Update of view before instead-of trigger.

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

Code Explanation

• Code line 34-38: Update the location of „XXX” to 'FRANCE’. Podniósł wyjątek, ponieważ instrukcje DML nie są dozwolone w widoku złożonym.

Output

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

ORA-06512: at line 2

Step 5)aby uniknąć napotkania błędu podczas aktualizacji widoku w poprzednim kroku, w tym kroku użyjemy „zamiast trigger.”

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

Wyjaśnienie kodu

• linia kodu 39: Utworzenie zamiast wyzwalacza zdarzenia 'UPDATE’ w widoku 'guru99_emp_view’ na poziomie wiersza. Zawiera instrukcję update, aby zaktualizować lokalizację w tabeli bazowej „dept”.
• linia kodu 44: instrukcja Update używa ’:NEW’ i ’: OLD’ aby znaleźć wartość kolumn przed i po aktualizacji.

Wyjście

Trigger Created

Krok 6) Aktualizacja widoku po wyzwalaczu. Teraz błąd nie pojawi się, ponieważ „zamiast wyzwalacza” zajmie się operacją aktualizacji tego złożonego widoku. A po wykonaniu kodu lokalizacja pracownika XXX zostanie zaktualizowana do” Francja ” Z ” Japonia.”

BEGINUPDATE 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”. Jest to pomyślne, ponieważ WYZWALACZ „zamiast” zatrzymał rzeczywistą instrukcję aktualizacji w widoku i przeprowadził aktualizację tabeli bazowej.
• linia kodu 55: weryfikacja zaktualizowanego rekordu.

:

procedura PL/SQL pomyślnie zakończona

CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

CREATE TRIGGER emp_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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;

ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> ALL TRIGGERS;