Oracle Триггер PL/SQL: Вместо составных типов &

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

Триггеры хранятся PL/SQL программы, которые запускаются Oracle двигатель автоматически при Операторы DML Такие операции, как вставка, обновление и удаление, выполняются в таблице или при возникновении определенных событий. Код, который должен быть выполнен в случае срабатывания триггера, может быть определен в соответствии с требованиями. Вы можете выбрать событие, при котором должен срабатывать триггер, и время его выполнения. Цель триггера — поддерживать целостность информации в базе данных.

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

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

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

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

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

Классификация по времени

• ДО триггера: Оно срабатывает до того, как произойдет указанное событие.
• ПОСЛЕ триггера: Он срабатывает после того, как произойдет указанное событие.
• ВМЕСТО триггера: Особый тип. Подробнее об этом вы узнаете в следующих разделах. (только для DML)

Классификация по уровню

• Триггер уровня оператора: Оно срабатывает один раз для указанного оператора события.
• Триггер уровня ROW: Эта операция срабатывает для каждой записи, затронутой указанным событием. (только для операций DML)

Классификация на основе события

• Триггер DML: Оно срабатывает при указании события DML (INSERT/UPDATE/DELETE).
• Триггер DDL: Оно срабатывает при указании события DDL (CREATE/ALTER).
• Триггер базы данных: Оно срабатывает при указании события базы данных (LOGON/LOGOFF/STARTUP/SHUTDOWN).

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

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

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

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;

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

• Приведенный выше синтаксис показывает различные необязательные операторы, которые присутствуют при создании триггера.
• В разделе "ДО/ПОСЛЕ" будет указано время проведения мероприятия.
• ВСТАВКА/ОБНОВЛЕНИЕ/ВХОД/СОЗДАНИЕ/и т. д. укажет событие, для которого должен сработать триггер.
• В предложении ON указывается объект, для которого допустимо вышеупомянутое событие. Например, это будет имя таблицы, для которой может произойти событие DML в случае срабатывания триггера DML.
• Команда «FOR EACH ROW» задаёт триггер на уровне строки.
• В пункте WHEN будет указано дополнительное условие, при котором должен сработать триггер.
• Часть, отвечающая за объявление кода, часть, отвечающая за выполнение, и часть, отвечающая за обработку исключений, идентичны аналогичным частям остальных компонентов. PL/SQL-блокиЧасть, посвященная декларации, и Обработка исключений Эти части являются необязательными.

:NEW и :OLD Пункт

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

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

• :НОВЫЙ – Во время выполнения триггера он сохраняет новое значение для столбцов базовой таблицы/представления.
• :СТАРЫЙ – Во время выполнения триггера он сохраняет старые значения столбцов базовой таблицы/представления.

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

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

Триггер «INSTEAD OF» — это особый тип триггера. Он используется только в триггерах DML. Он применяется, когда любое событие DML должно произойти в сложном представлении.

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

Пример 1: В этом примере мы создадим сложное представление на основе двух базовых таблиц, где Table_1 — это таблица сотрудников, а Table_2 — таблица отделов.

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

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

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

На скриншоте ниже показано создание базовых таблиц 'emp' и 'dept' в Oracle.

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

Code объяснение

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

Выход:

Table Created

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

На скриншоте ниже показаны примеры строк, вставляемых в таблицы 'dept' и 'emp'.

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code объяснение

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

Выход:

PL/SQL procedure completed

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

На скриншоте ниже показано создание и последующий запрос к сложному представлению.

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;

Code объяснение

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

Выход:

View created

Шаг 4) Обновление представления перед срабатыванием триггера INSTEAD OF.

На скриншоте ниже показана попытка обновления сложного представления и возникшая в результате ошибка.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code объяснение

• Code строки 34-38: Измените местоположение "XXX" на 'FRANCE'. Возникло исключение, поскольку операторы DML не допускаются непосредственно в сложном представлении.

Выход:

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

ORA-06512: at line 2

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

На скриншоте ниже показано создание триггера INSTEAD OF.

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

Code объяснение

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

Выход:

Trigger Created

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

На скриншоте ниже показано успешное обновление с помощью триггера INSTEAD OF и обновленное представление.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code Объяснение:

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

Выход:

PL/SQL procedure successfully completed

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

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

• ПЕРЕД ЗАЯВЛЕНИЕМ – уровень
• ПЕРЕД СТРОКОМ – уровень
• ПОСЛЕ СТРОКА – уровень
• ПОСЛЕ ЗАЯВЛЕНИЯ – уровень

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

На скриншоте ниже показан синтаксис составного триггера с четырьмя секциями синхронизации.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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;

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

• Приведенный выше синтаксис демонстрирует создание составного триггера.
• Декларативный раздел является общим для всех блоков выполнения в теле триггера.
• Эти четыре блока синхронизации могут располагаться в любой последовательности. Наличие всех четырех блоков синхронизации не является обязательным. Мы можем создать составной триггер только для тех временных интервалов, которые необходимы.

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

На скриншоте ниже показан пример составного триггера и его результат.

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;

Code Объяснение:

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

Выход:

Trigger created

PL/SQL procedure successfully completed.

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

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

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

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

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

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

Часто задаваемые вопросы (FAQ)