Oracle Учебное пособие по триггерам PL/SQL: вместо составного [Пример]

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

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

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

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

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

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

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

• Классификация на основе синхронизация
• Триггер BEFORE: срабатывает до того, как произойдет указанное событие.
• AFTER Trigger: срабатывает после того, как произошло указанное событие.
• ВМЕСТО триггера: специальный тип. Вы узнаете больше о дальнейших темах. (только для DML)
• Классификация на основе уровень
• Триггер уровня STATEMENT: срабатывает один раз для указанного оператора события.
• Триггер уровня ROW: он срабатывает для каждой записи, на которую повлияло указанное событие. (только для ДМЛ)
• Классификация на основе События
• Триггер DML: срабатывает, когда указано событие DML (INSERT/UPDATE/DELETE).
• Триггер DDL: срабатывает, когда указано событие DDL (CREATE/ALTER).
• Триггер DATABASE: срабатывает, когда указано событие базы данных (ВХОД/ВЫХОД/ЗАПУСК/ЗАВЕРШЕНИЕ).

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

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

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

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;

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

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

:NEW и :OLD Пункт

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

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

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

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

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

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

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

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

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

• Table_1 — это таблица emp, а

• Table_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: Создание таблицы «отдел».

Результат

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

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

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,'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: Вставка данных в таблицу «отдел».
• Строка кода 20–26: Вставка данных в таблицу emp.

Результат

PL/SQL-процедура завершенный

Шаг 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;

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

• Строка кода 27–32: Создание представления «guru99_emp_view».

• Строка кода 33: Запрос guru99_emp_view.

Результат

Представление создано

ИМЯ СОТРУДНИКА	DEPT_NAME	Местонахождения:
ZZZ	HR	США
YYY	ПРОДАЖИ	UK
XXX	ФИНАНСОВАЯ	ЯПОНИЯ

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

BEGIN
UPDATE 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_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;
/

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

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

Результат

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

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

BEGIN
UPDATE 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	США
YYY	ПРОДАЖИ	UK
XXX	ФИНАНСОВАЯ	ФРАНЦИЯ

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

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

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

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

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

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

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

Пример 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;

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

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

Вывод:

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

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

EMP_NAME	ЭМП_НО	ЗАРПЛАТА	МЕНЕДЖЕР	DEPT_NO
CCC	1004	5000	AAA	30

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

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

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

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

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

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

Итого

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