Учебное пособие по Oracle PL / SQL Trigger: вместо составного (пример)

Содержание:

Anonim

Что такое триггер в PL / SQL?

ТРИГГЕРЫ - это сохраненные программы, которые автоматически запускаются механизмом Oracle, когда в таблице выполняются такие операторы DML, как вставка, обновление, удаление или происходят некоторые события. Код, который должен выполняться в случае срабатывания триггера, может быть определен в соответствии с требованиями. Вы можете выбрать событие, при котором должен срабатывать триггер, и время выполнения. Цель триггера - поддерживать целостность информации в базе данных.

В этом руководстве вы узнаете:

  • Преимущества триггеров
  • Типы триггеров в Oracle
  • Как создать триггер
  • : NEW и: OLD Пункт
  • ВМЕСТО триггера
  • Составной триггер

Преимущества триггеров

Ниже приведены преимущества триггеров.

  • Автоматическое создание некоторых производных значений столбцов
  • Обеспечение ссылочной целостности
  • Регистрация событий и хранение информации о доступе к таблицам
  • Аудиторская проверка
  • Синхронная репликация таблиц
  • Введение авторизации безопасности
  • Предотвращение недействительных транзакций

Типы триггеров в Oracle

Триггеры можно классифицировать по следующим параметрам.

  • Классификация по срокам
    • BEFORE Trigger: срабатывает до того, как произошло указанное событие.
    • ПОСЛЕ триггера: срабатывает после того, как произошло указанное событие.
    • ВМЕСТО триггера: особый тип. Вы узнаете больше о дальнейших темах. (только для DML)
  • Классификация по уровню
    • Триггер уровня STATEMENT: срабатывает один раз для указанного оператора события.
    • Триггер уровня ROW: он срабатывает для каждой записи, на которую повлияло указанное событие. (только для DML)
  • Классификация по событию
    • Триггер DML: срабатывает, когда указано событие DML (INSERT / UPDATE / DELETE)
    • Триггер DDL: срабатывает, когда указано событие DDL (CREATE / ALTER)
    • Триггер DATABASE: срабатывает, когда указано событие базы данных (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Таким образом, каждый триггер представляет собой комбинацию вышеуказанных параметров.

Как создать триггер

Ниже приведен синтаксис для создания триггера.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Объяснение синтаксиса:

  • Приведенный выше синтаксис показывает различные необязательные операторы, которые присутствуют при создании триггера.
  • BEFORE / AFTER укажет время события.
  • ВСТАВИТЬ / ОБНОВЛЕНИЕ / ВХОД / СОЗДАТЬ / и т. Д. будет указывать событие, для которого необходимо активировать триггер.
  • Предложение ON указывает, на каком объекте действует упомянутое выше событие. Например, это будет имя таблицы, в которой может произойти событие DML в случае триггера DML.
  • Команда «ДЛЯ КАЖДОЙ СТРОКИ» задает триггер уровня СТРОКИ.
  • Предложение WHEN определяет дополнительное условие, при котором триггер должен срабатывать.
  • Часть объявления, часть выполнения, часть обработки исключений такая же, как и у других блоков PL / SQL. Часть объявления и часть обработки исключений не являются обязательными.

: NEW и: OLD Пункт

В триггере уровня строки триггер срабатывает для каждой связанной строки. Иногда требуется знать значение до и после оператора DML.

Oracle предоставила два предложения в триггере уровня RECORD для хранения этих значений. Мы можем использовать эти предложения для ссылки на старые и новые значения внутри тела триггера.

  • : NEW - содержит новое значение для столбцов базовой таблицы / представления во время выполнения триггера.
  • : OLD - содержит старое значение столбцов базовой таблицы / представления во время выполнения триггера.

Это предложение следует использовать на основе события DML. В таблице ниже будет указано, какое предложение действительно для какого оператора DML (INSERT / UPDATE / DELETE).

ВСТАВЛЯТЬ ОБНОВИТЬ УДАЛИТЬ
:НОВЫЙ ДЕЙСТВИТЕЛЬНЫЙ ДЕЙСТВИТЕЛЬНЫЙ ИНВАЛИД. В случае удаления нет нового значения.
:СТАРЫЙ ИНВАЛИД. В регистре вставки нет старого значения ДЕЙСТВИТЕЛЬНЫЙ ДЕЙСТВИТЕЛЬНЫЙ

ВМЕСТО триггера

«ВМЕСТО триггера» - это особый тип триггера. Используется только в триггерах DML. Он используется, когда на сложном представлении должно произойти какое-либо событие DML.

Рассмотрим пример, в котором представление состоит из трех базовых таблиц. Когда какое-либо событие DML выдается в этом представлении, оно становится недействительным, поскольку данные берутся из 3 разных таблиц. Таким образом, в данном случае используется триггер INSTEAD OF. Триггер INSTEAD OF используется для непосредственного изменения базовых таблиц вместо изменения представления для данного события.

Пример 1 : В этом примере мы собираемся создать сложное представление из двух базовых таблиц.

  • Table_1 - это таблица emp и
  • Таблица_2 - таблица отдела.

Затем мы увидим, как триггер INSTEAD OF используется для выполнения UPDATE оператора подробностей местоположения в этом сложном представлении. Мы также увидим, как: NEW и: OLD полезны в триггерах.

  • Шаг 1. Создание таблиц emp и dept с соответствующими столбцами.
  • Шаг 2. Заполнение таблицы примерными значениями
  • Шаг 3: Создание представления для созданной выше таблицы
  • Шаг 4. Обновление представления перед триггером вместо
  • Шаг 5: Создание триггера вместо
  • Шаг 6. Обновление вида после триггера вместо триггера

Шаг 1) Создание таблицы emp и dept с соответствующими столбцами

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

Код Пояснение

  • Строка кода 1-7 : создание таблицы emp.
  • Строка кода 8-12 : Создание таблицы "dept".

Выход

Таблица создана

Шаг 2) Теперь, когда мы создали таблицу, мы заполним эту таблицу примерными значениями и созданием представлений для вышеуказанных таблиц. 

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

Код Пояснение

  • Строка кода 13-19 : Вставка данных в таблицу dept.
  • Строка кода 20-26: Вставка данных в таблицу emp.

Выход

Процедура PL / SQL завершена

Шаг 3) Создание представления для созданной выше таблицы. 

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;

Код Пояснение

  • Строка кода 27-32: Создание представления guru99_emp_view.
  • Строка кода 33: Запрос guru99_emp_view.

Выход

Просмотр создан

ИМЯ СОТРУДНИКА DEPT_NAME МЕСТО РАСПОЛОЖЕНИЯ
ZZZ HR Соединенные Штаты Америки
ГГГ ПРОДАЖИ Великобритания
XXX ФИНАНСОВЫЕ ЯПОНИЯ

Шаг 4) Обновление вида до, а не триггера.

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

Код Пояснение

  • Строка кода 34–38: Обновите местоположение «XXX» на «FRANCE». Это вызвало исключение, потому что операторы DML не разрешены в сложном представлении.

Выход

ORA-01779: невозможно изменить столбец, который сопоставляется с таблицей без сохранения ключей

ORA-06512: в строке 2

Шаг 5) Чтобы избежать появления ошибки во время обновления представления на предыдущем шаге, на этом шаге мы будем использовать «вместо триггера».

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

Код Пояснение

  • Строка кода 39: Создание триггера INSTEAD OF для события «UPDATE» в представлении «guru99_emp_view» на уровне ROW. Он содержит оператор обновления для обновления местоположения в базовой таблице dept.
  • Строка кода 44: Оператор обновления использует ': NEW' и ': OLD' для поиска значений столбцов до и после обновления.

Выход

Триггер создан

Шаг 6) Обновление вида после триггера. Теперь ошибка не возникнет, поскольку «вместо триггера» будет обрабатывать операцию обновления этого сложного представления. И когда код будет выполнен, местоположение сотрудника XXX будет обновлено до «Франция» из «Японии».

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

Пояснение к коду:

  • Строка кода 49-53: Обновление местоположения «XXX» на «FRANCE». Это успешно, потому что триггер INSTEAD OF остановил фактический оператор обновления на представлении и выполнил обновление базовой таблицы.
  • Строка кода 55: Проверка обновленной записи.

Выход:

Процедура PL / SQL успешно завершена

ИМЯ СОТРУДНИКА DEPT_NAME МЕСТО РАСПОЛОЖЕНИЯ
ZZZ HR Соединенные Штаты Америки
ГГГ ПРОДАЖИ Великобритания
XXX ФИНАНСОВЫЕ ФРАНЦИЯ

Составной триггер

Составной триггер - это триггер, который позволяет вам определять действия для каждой из четырех временных точек в теле одного триггера. Четыре различных точки синхронизации, которые он поддерживает, указаны ниже.

  • ПЕРЕД ЗАЯВЛЕНИЕМ - уровень
  • BEFORE ROW - уровень
  • ПОСЛЕ РЯДА - уровень
  • ПОСЛЕ ЗАЯВЛЕНИЯ - уровень

Это дает возможность комбинировать действия для разного времени в один и тот же триггер.

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;END AFTER STATEMENT;END;

Объяснение синтаксиса:

  • Приведенный выше синтаксис показывает создание триггера COMPOUND.
  • Декларативный раздел является общим для всего исполнительного блока в теле триггера.
  • Эти 4 блока синхронизации могут быть в любой последовательности. Необязательно иметь все эти 4 блока синхронизации. Мы можем создать СЛОЖНЫЙ триггер только на то время, которое требуется.

Пример 1. В этом примере мы собираемся создать триггер для автоматического заполнения столбца зарплаты значением по умолчанию 5000.

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN: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;

Пояснение к коду:

  • Строка кода 2-10 : Создание составного триггера. Он создается для отсчета времени ПЕРЕД ROW- level, чтобы заполнить зарплату значением по умолчанию 5000. Это изменит зарплату на значение по умолчанию «5000» перед вставкой записи в таблицу.
  • Строка кода 11-14 : Вставить запись в таблицу emp.
  • Строка кода 16 : Проверка вставленной записи.

Выход:

Триггер создан

Процедура PL / SQL успешно завершена.

EMP_NAME EMP_NO ЗАРПЛАТА УПРАВЛЯЮЩИЙ ДЕЛАМИ DEPT_NO
CCC 1004 5000 AAA 30

Включение и отключение триггеров

Триггеры могут быть включены или отключены. Чтобы включить или отключить триггер, необходимо задать оператор ALTER (DDL) для триггера, который его отключает или включает.

Ниже приведен синтаксис включения / отключения триггеров.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Объяснение синтаксиса:

  • Первый синтаксис показывает, как включить / выключить одиночный триггер.
  • Второй оператор показывает, как включить / отключить все триггеры в определенной таблице.

Резюме

В этой главе мы узнали о триггерах PL / SQL и их преимуществах. Мы также изучили различные классификации и обсудили триггер INSTEAD OF и триггер COMPOUND.