Скачать презентацию Тема Триггеры PL SQL Вопросы 1 2 3 4 Скачать презентацию Тема Триггеры PL SQL Вопросы 1 2 3 4

4 a PL SQL Триггеры.ppt

  • Количество слайдов: 28

Тема: Триггеры PL/SQL Вопросы: 1. 2. 3. 4. 5. 6. Характеристика и типы триггеров Тема: Триггеры PL/SQL Вопросы: 1. 2. 3. 4. 5. 6. Характеристика и типы триггеров Триггеры DML Операторные триггеры DML Строковые триггеры DML Триггеры замещения Системные триггеры 1

Литература 1. Скотт Урман. Oracle 9 i. Программирование на языке PL/SQL. М. : «ЛОРИ» Литература 1. Скотт Урман. Oracle 9 i. Программирование на языке PL/SQL. М. : «ЛОРИ» . 2004. – 528 с. 2

Характеристика и типы триггеров Триггеры аналогичны процедурам и функциям, также являются поименованными блоками PL/SQL Характеристика и типы триггеров Триггеры аналогичны процедурам и функциям, также являются поименованными блоками PL/SQL и имеют разделы объявлений, выполняемый и обработки исключений. Триггер запускается автоматически при возникновении определенных событий: • операции DML (ISERT, UPDATE или DELETE), выполняемой над таблицей или представлением; • системного события, например, запуска или останова БД; • определенных видов операций DDL. 3

Характеристика и типы триггеров Назначение триггеров: • реализация сложных ограничений целостности данных; • контроль Характеристика и типы триггеров Назначение триггеров: • реализация сложных ограничений целостности данных; • контроль за информацией в таблицах путем регистрации изменений и пользователей; • оповещение других программ о необходимых действиях при изменении информации в таблице; • сложный контроль защиты данных или специальный аудит; • публикация информации о событиях. Типы триггеров: • триггеры DML (операторные или строковые); • триггеры замещения; • системные триггеры. 4

Триггеры DML Триггер DML запускается оператором DML при выполнении операций вставки (INSERT), удаления (DELETE Триггеры DML Триггер DML запускается оператором DML при выполнении операций вставки (INSERT), удаления (DELETE ) или модификации (UPDATE) данных таблицы. Они активизируются до или после операции, на уровне оператора или строки. Событие, управляющее запуском триггера, описывается в виде логических условий. Триггер может запускаться и несколькими операторами, но хотя бы один оператор из трех должен быть указан в условии запуска триггера. 5

Типы триггеров DML Категории Значение Комментарии Оператор DML, активирующий триггер Оператор INSERT UPDATE DELETE Типы триггеров DML Категории Значение Комментарии Оператор DML, активирующий триггер Оператор INSERT UPDATE DELETE Момент времени BEFORE AFTER Момент активации триггера: до или после выполнения оператора Уровень Строка или оператор Строковый триггер активируется один раз для каждой из строк, на которую воздействует оператор, вызывающий срабатывание триггера (опция FOR EACH ROW) Операторный триггер активируется один раз до или после выполнения оператора 6

Порядок активизации триггеров DML Триггеры активизируются при выполнении оператора DML. Алгоритм выполнения оператора DML: Порядок активизации триггеров DML Триггеры активизируются при выполнении оператора DML. Алгоритм выполнения оператора DML: 1. Выполняются операторные триггеры BEFORE (при наличии). 2. Для каждой строки, на которую действует оператор: A. Выполняются строковые триггеры BEFORE (при наличии). B. Выполняется собственно оператор DML. C. Выполняются строковые триггеры AFTER (при наличии). 3. Выполняются операторные триггеры AFTER (при 7 наличии).

Триггеры DML Если триггер запускается при выполнении оператора UPDATE, то для условий срабатывания могут Триггеры DML Если триггер запускается при выполнении оператора UPDATE, то для условий срабатывания могут быть указаны конкретные изменяемые столбцы. Для управления триггерами предназначены операторы, которые могут переключать режим, разрешая или запрещая запуск триггера: — ALTER TRIGGER (изменяется режим указанного триггера) с опцией DISABLE или ENABLE и ALTER TABLE (переключается режим у всех триггеров, связанных с таблицей). 8

Создание триггеров Оператор определения триггера имеет формат: CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE | Создание триггеров Оператор определения триггера имеет формат: CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE | AFTER} [имя_схемы. ] {INSERT | DELETE | UPDATE [OF имя_столбца [ , имя_столбца. . . ]] } [OR {INSERT | DELETE | UPDATE [OF имя_столбца [ , имя_столбца. . . ]] }. . . ] ON [имя_схемы. ]{имя_таблицы имя_представления } [FOR EACH ROW] [WHEN условие ] программа_на_ PL/SQL 9

Создание триггеров Ключевое слово ON задает имя таблицы, ассоциированной с триггером. Необязательное ключевое слово Создание триггеров Ключевое слово ON задает имя таблицы, ассоциированной с триггером. Необязательное ключевое слово FOR EACH ROW определяет триггер как строковый. Необязательное ключевое слово WHEN задает дополнительное логическое условие, сужающее область событий, при наступлении которых триггер запускается. 10

Операторные триггеры - пример Пример. В среде SQL *Plus создадим таблицу: SQL> CREATE TABLE Операторные триггеры - пример Пример. В среде SQL *Plus создадим таблицу: SQL> CREATE TABLE MILLER. ADT 2 ( 3 USAL VARCHAR 2(50), 4 TISP DATE 5 ) 6 / Таблица создана. С ее помощью и операторного триггера организуем аудит доступа к таблице MILLER. CUSTOMERS. 11

Операторные триггеры - пример SQL> CREATE OR REPLACE TRIGGER test. Trg 2 AFTER INSERT Операторные триггеры - пример SQL> CREATE OR REPLACE TRIGGER test. Trg 2 AFTER INSERT OR DELETE OR UPDATE ON customers 3 4 DECLARE 5 6 BEGIN 7 8 INSERT INTO MILLER. ADT(USAL, TISP) 9 VALUES(USER, SYSDATE); 10 11 END test. Trg; 12 /. 12

Операторные триггеры - пример Добавим запись в таблицу MILLER. CUSTOMERS SQL> INSERT INTO CUSTOMERS(cust_num, Операторные триггеры - пример Добавим запись в таблицу MILLER. CUSTOMERS SQL> INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit) 2 VALUES (2200, 'My. Company', 107, 555. 5643) 3 / 1 строка создана. В таблице аудита MILLER. ADT SQL> SELECT USAL, TO_CHAR(TISP, 'DD/MM/YYYY, HH 24: MI: SS') TISM 2 FROM ADT 3 / Получим: USAL TISM -------------------------- MILLER 17/03/2004, 15: 25: 12 13

Операторные и строковые триггеры - пример CREATE TABLE MILLER. TSTTRIG ( ID NUMBER PRIMARY Операторные и строковые триггеры - пример CREATE TABLE MILLER. TSTTRIG ( ID NUMBER PRIMARY KEY, NM VARCHAR 2(50), ROD VARCHAR 2(50), INRW DATE ) Поля таблицы MILLER. TSTTRIG: INSERT INTO TSTTRIG (ID, NM, ROD, INRW) VALUES (7369, 'SMITH', 'CLERK', TO_DATE('17 -2 -2000', 'DD-MM-YYYY')) VALUES (7370, 'JONES', 'MANAGER', TO_DATE('2 -4 -2001', 'DD-MM-YYYY')) VALUES (7371, 'MILLER', 'SALESMAN', TO_DATE('20 -32003', 'DD-MM-YYYY')) VALUES (7372, 'SCOTT', 'ANALYST', TO_DATE('09 -122001', 'DD-MM-YYYY')) 14

Операторные и строковые триггеры - пример Создадим для MILLER. TSTTRIG два операторных триггера на Операторные и строковые триггеры - пример Создадим для MILLER. TSTTRIG два операторных триггера на моменты BEFORE и AFTER для события UPDATE: CREATE OR REPLACE TRIGGER BFOTST BEFORE UPDATE ON TSTTRIG DECLARE BEGIN INSERT INTO ADT(USAL, TISP, WDO, PRIM) VALUES(USER, SYSDATE, 'Update‘, 'Before Statement trigger'); END BFOTST; CREATE OR REPLACE TRIGGER AFTTST AFTER UPDATE ON TSTTRIG DECLARE BEGIN INSERT INTO ADT(USAL, TISP, WDO, PRIM) VALUES(USER, SYSDATE, 'Update', 'After Statement trigger'); END AFTTST; 15

Операторные и строковые триггеры - пример Создадим строковые триггеры по тому же принципу, но Операторные и строковые триггеры - пример Создадим строковые триггеры по тому же принципу, но отличаются они наличием FOR EACH ROW. CREATE OR REPLACE TRIGGER BFOTSTR BEFORE UPDATE ON TSTTRIG FOR EACH ROW DECLARE BEGIN INSERT INTO ADT(USAL, TISP, WDO, PRIM) VALUES(USER, SYSDATE, 'Update', 'Before Row trigger'); END BFOTSTR; 16

Операторные и строковые триггеры - пример CREATE OR REPLACE TRIGGER AFTTSTR AFTER UPDATE ON Операторные и строковые триггеры - пример CREATE OR REPLACE TRIGGER AFTTSTR AFTER UPDATE ON TSTTRIG FOR EACH ROW DECLARE BEGIN INSERT INTO ADT(USAL, TISP, WDO, PRIM) VALUES(USER, SYSDATE, 'Update', 'After Row trigger'); END AFTTSTR; Введем оператор, для таблицы MILLER. TSTTRIG: UPDATE MILLER. TSTTRIG SET ROD = 'SPOOKY' WHERE ID IN (7369, 7370) Изменено две строки! Так как условие оператора UPDATE соответствует в нашем случае двум записям. 17

Строковые триггеры - пример содержимое таблицы MILLER. ADT: SQL> SELECT * FROM MILLER. ADT Строковые триггеры - пример содержимое таблицы MILLER. ADT: SQL> SELECT * FROM MILLER. ADT 2 / USAL TISP WDO PRIM ----------------------MILLER 17. 03. 2004 Update Before Statement trigger MILLER 17. 03. 2004 Update Before Row trigger MILLER 17. 03. 2004 Update After Statement trigger 6 rows selected Операторный триггер сработал два раза на BEFORE и AFTER, а строковый четыре раза! Потому, что изменили две строки! 18

Замещающие триггеры создаются только для представлений. В отличие от триггеров DML, выполняемых в дополнение Замещающие триггеры создаются только для представлений. В отличие от триггеров DML, выполняемых в дополнение к операторам DML, замещающие триггеры выполняются вместо операторов DML, вызывающих их срабатывание. Замещающий триггер должен быть строковым. Если представление является не модифицируемым, то для него можно создать замещающий триггер, выполняющий нужные действия и тем самым разрешающий его модификацию. Замещающий триггер можно создать и для модифицируемого представления, если требуется дополнительная обработка информации.

Пример замещающего триггера Создадим представление classes_rooms : CREATE OR REPLACE VIEW classes_rooms AS SELECT Пример замещающего триггера Создадим представление classes_rooms : CREATE OR REPLACE VIEW classes_rooms AS SELECT department, course, building, room_number FROM rooms, classes WHERE rooms. room_id = classes. room_id; Ввести информацию в это представление нельзя. Над ним можно выполнить операции обновления или удаления данных, но, скорее всего, эти действия будут некорректны. Например, в результате выполнения над classes_rooms операции DELETE будут удалены соответствующие строки из classes.

Пример замещающего триггера Создадим триггер замещения и с его помощью выполним обновление базовых таблиц: Пример замещающего триггера Создадим триггер замещения и с его помощью выполним обновление базовых таблиц: CREATE TRIGGER Classes. Rooms. Insert INSTEAD OF INSERT ON classes_rooms DECLARE v_room. ID rooms. room_id%TYPE; BEGIN -- Определим идентификатор аудитории. SELECT room_id INTO v_room. ID FROM rooms WHERE building = : new. building AND room_number = : new. room_number;

Пример замещающего триггера -- Обновим группу. UPDATE CLASSES SET room_id = v_rooml. D WHERE Пример замещающего триггера -- Обновим группу. UPDATE CLASSES SET room_id = v_rooml. D WHERE department = : new. department AND course = : new. course; END Classes. Rooms. Insert; С помощью триггера Classes. Rooms. Insert оператор INSERT выполняется успешно.

Системные триггеры активизируются на события DDL или самой БД. К событиям DDL относятся операторы Системные триггеры активизируются на события DDL или самой БД. К событиям DDL относятся операторы CREATE, DROP, ALTER. К событиям базы данных относятся: - запуск останов сервера, регистрация отключение пользователя БД, ошибка сервера. Формат создания системного триггера: CREATE OR REPLACE TRIGGER [схема. ]имя_триггера {BEFORE | AFTER} {список_событий_DDL | список_событий_базы_данных} ON {DATABASE | [схема. ]SCHEMA} конструкция_REFERENCING [условие_WHEN] тело триггера; 23

Системные триггеры Где: список_событий_DDL - одно или несколько событий DDL (разделяемых OR) список_событий_базы_данных - Системные триггеры Где: список_событий_DDL - одно или несколько событий DDL (разделяемых OR) список_событий_базы_данных - одно или несколько событий БД (разделяемых OR) Событие STARTUP SHUTDOWN Время Активизируется AFTER После запуска экземпляра BEFORE При остановке экземпляра. Событие не активизирует триггер, если останов БД аварийный! SERVERERROR AFTER При возникновении ошибки. 24

Системные триггеры Событие LOGON LOGOOFF Время Активизируется AFTER После успешного соединения пользователя с базой Системные триггеры Событие LOGON LOGOOFF Время Активизируется AFTER После успешного соединения пользователя с базой данных. BEFORE В начале отключения пользователя. DROP BEFORE, AFTER ALTER BEFORE, AFTER CREATE До и после создания объекта. До и после удаления объекта. До и после изменения объекта. 25

Системные триггеры - пример Пример. Системный аудит. Запускаем *SQL/Plus с правами SYSTEM. Создадим таблицу Системные триггеры - пример Пример. Системный аудит. Запускаем *SQL/Plus с правами SYSTEM. Создадим таблицу для фиксации регистрации пользователей на уровне БД: SQL> CREATE TABLE SYSTEM. AUDTBASE 2 ( 3 NZAP NUMBER, 4 POLZ VARCHAR 2(20), 5 TMIN DATE, 6 OPER VARCHAR 2(50) 7 ) 8 / 26

Системные триггеры - пример Пример. Системный аудит. Создадим триггер: SQL> CREATE OR REPLACE TRIGGER Системные триггеры - пример Пример. Системный аудит. Создадим триггер: SQL> CREATE OR REPLACE TRIGGER FIXUSERIN 2 AFTER LOGON ON DATABASE 3 4 BEGIN 5 6 INSERT INTO SYSTEM. AUDTBASE(NZAP, POLZ, TMIN, OPER) 7 VALUES(1, USER, SYSDATE, 'User. Is. Log(off)'); 8 9 END FIXUSERIN; 10 / Триггер создан. 27

Системные триггеры - пример Пример. Системный аудит. Просмотрим содержимое таблицы в первом сеансе: SQL> Системные триггеры - пример Пример. Системный аудит. Просмотрим содержимое таблицы в первом сеансе: SQL> SELECT NZAP, POLZ, TO_CHAR(TMIN, 'DD. MM. YYYY HH 24: MI: SS'), OPER FROM SYSTEM. AUDTBASE 2 / 3 Результат: NZAP POLZ TO_CHAR(TMIN, 'DD. MM. YYYYHH 24: M OPER -----------------------1 SYS 22. 03. 2003 16: 35: 50 User. Is. Log(off) 1 SYS 22. 03. 2003 16: 37: 38 User. Is. Log(off) 1 MILLER 22. 03. 2003 16: 38: 44 User. Is. Log(off) 1 MILLER 22. 03. 2003 16: 38: 46 User. Is. Log(off) 6 строк выбрано. 28