21) Триггеры в PL / SQL

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

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

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) Теперь, когда мы создали таблицу, мы заполним эту таблицу примерами значений и созданием представлений для вышеуказанных таблиц.

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 : вставка данных в таблицу «dept».
• Строка кода 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.

Вывод

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

Шаг 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» на уровне ROW. Он содержит оператор update для обновления местоположения в базовой таблице «dept».
• Строка кода 44: оператор Update использует «: 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: проверка обновленной записи.

Вывод:

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;

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;

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