PL/SQL Триггер блок PL/SQL, выполняемый неявно каждый раз, когда происходит конкретное событие. - презентация

1 PL/SQL

2 Триггер блок PL/SQL, выполняемый неявно каждый раз, когда происходит конкретное событие

3 DML-события, которые происходят при выполнении инструкций INSERT, UPDATE или DELETE. DDL-события, которые происходят при выполнении инструкций CREATE, ALTER или DROP. Другие события уровня базы данных.

4 CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE | AFTER | INSTEAD OF } событие_триггера [OR событие_2 [OR событие_3]] ON {таблица_или_представление | NESTED TABLE столбец_вложенной_таблицы OF представление} [REFERENCING [OLD AS старая][NEW AS новая] [PARENT AS родитель] ] [FOR EACH ROW ][WHEN условие_триггера]

5 DML-события Событие Описание. Событие происходит тогда, когда … INSERT… в таблицу или представление добавляется строка UPDATE… инструкция UPDATE изменяет таблицу или представление UPDATE OF имя_поля … обновляется указанное поле таблицы, причём для каждой строки – отдельное событие. DELETE… строка удаляется из таблицы или представления. При выполнении инструкции TRUNCATE это событие не происходит.

6 CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE | AFTER} событие_триггера ON [DATABASE | схема] [WHEN условие_триггера] тело_триггера; END имя_триггера;

7 DDL-события ALTER… инструкция ALTER изменяет объект базы данных. В этом контексте объекты подобны таблицам или пакетам (в ALL_OBJECTS). Может применяться к отдельной схеме или ко всей базе данных. ANALIZE… база данных собирает или удаляет статистику или проверяет структуру объекта базы данных. ASSOCIATE STATISTICS … база данных ассоциирует тип статистики с объектом базы данных. AUDIT… база данных записывает операцию аудита. COMMENT… изменяется комментарий к объекту базы данных. CREATE… создаётся объект базы данных. Не происходит при выполнении инструкции CREATE CONTROLFILE

8 DDL-события DIASSOCIATE STATISTICS … база данных отсоединяет тип статистики от объекта базы данных. DROP… инструкция DROP удаляет объект из БД. В этом контексте объекты подобны таблицам или пакетам (в ALL_OBJECTS). Может применяться к отдельной схеме или ко всей базе данных. GRANT… назначается привилегия системе, роли или объекту. NOAUDIT… база данных выполняет инструкцию NOAUDIT. RENAME… инструкция RENAME изменяет имя объекта БД. REVOKE… аннулируется привилегия системы, роли или объекта. TRUNCATE… выполняется инструкция TRUNCATE для очистки таблицы или кластера от ненужных данных.

9 SERVERERROR… регистрируется сообщение об ошибке сервера. LOGON… создаётся сеанс (пользователь подключается к БД). LOGOFF… закрывается сеанс (пользователь отключается от БД). STARTUP… открывается БД. SHUTDOWN… закрывается БД. SUSPEND… из-за ошибки сервера зависает транзакция. DDL…при выполнении DDL-операции

10 CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON emp BEGIN IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR (TO_CHAR(sysdate,'HH24') NOT BETWEEN '08' AND '18') THEN RAISE_APPLICATION ERROR (-20500, It is not working time. Be funny') ; END IF; END;

11 Псевдополя :new :old

12 CREATE OR REPLACE TRIGGER history_values AFTER DELETE OR INSERT OR UPDATE ON tab1 FOR EACH ROW BEGIN INSERT INTO history_tab (user_name, timestamp, id, old_val1, new_val1, old_val2, new_val2) VALUES (USER, SYSDATE, :old.id, :old.val1, :new.val1, :old.val2, :new.val2); END;

13 CREATE OR REPLACE TRIGGER history_values AFTER DELETE OR INSERT OR UPDATE ON tab1 REFERENCING OLD AS OLDR NEW AS NEWR FOR EACH ROW BEGIN INSERT INTO history_tab (user_name, timestamp, id, old_val1, new_val1, old_val2, new_val2) VALUES (USER, SYSDATE, oldr.id, oldr.val1, newr.val1, oldr.val2,newr.val2); END;

14 CREATE OR REPLACE TRIGGER derive_commission_pct BEFORE INSERT OR UPDATE OF sal ON emp FOR EACH ROW WHEN (new.job = 'SALESMAN') BEGIN IF INSERTING THEN :new.comm := 0; ELSE /* UPDATE of salary */ IF :old.comm IS NULL THEN :new.comm := 0 ; ELSE :new.comm := :old.comm * (:new.sal/:old.sal); END IF; END;

15 Порядок выполнения триггеров 1. Триггер BEFORE уровня инструкций. 2. Для каждой строки, обрабатываемой инструкцией. 3. Триггер BEFORE уровня строки. 4. Сама инструкция, запускающая триггер. 5. Триггер AFTER уровня строки. 6. Триггер AFTER уровня инструкций.

16 Если внутри триггера возникает сбой, и это исключение не обрабатывается явно, то все действия, выполненные в связи с исходной SQL- командой, отменяются, включая действия, выполненные сработавшими триггерами.

17 Виды коллекций Ассоциативные массивы(индексные таблицы) Вложенные таблицы Массивы переменной длины

18 Массив переменной длины TYPE имя_типа IS {VARRAY|VARYING ARRAY} (предельный размер) OF тип_элемента [NOT NULL];

19 Declare TYPE int_varray ID VARRAY (3) of INTEGER; varray_integer INT_VARRAY :=int_varray(); BEGIN FOR I in 1..3 LOOP Varray_integer.EXTEND; Varray_integer(i):=I; END LOOP

20 Определение массивов переменной длины как объектных типов CREATE OR REPLACE TYPE имя_типа AS VARRAY|VARYNG ARRAY (предельный_размер) OF тип_элемента [NOT NULL]

21 Определение массивов переменной длины в таблицах CREATE OR REPLACE TYPE address_varray AS VARRAY (3); CREATE TABLE addresses (address_idintegernot null, Street_addressaddress_varraynot null, Constraint address_pk PRIMARY KEY (address_id));

22 Insert into addresses Values (1, address_varray(Minsk,Brovki str.,6));

23 Select column_value FROM THE ( select cast(street_address as varray_nested_table) From addresses Where address_id=1)

24 Вложенные таблицы CREATE OR REPLACE TYPE имя_типа AS TABLE OF тип_элемента [NOT NULL]

25 Ассоциативные массивы CREATE OR REPLACE TYPE имя_типа AS TABLE OF тип_элемента [NOT NULL] INDEX BY[PLS_INTEGER|BINARY_INTEGER|VARCHAR2(размер)]

26 МетодС чем работает COUNTВсе Delete{(n)|(n,m)}Все Exists(n)Все Extend{|(n)|(n,i)}Массив переменной длины или вложенная таблица FIRSTВсе LASTВсе Limit Массив переменной длины

27 NEXT(n)Все PRIOR(n)Все Trim Все Trim(n)Все