Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Oracle > Мутирующая таблица

Автор: Rutti 5.5.2021, 22:26

Ребята, нужна ваша помощь!

Необходимо обойти проблему мутирующей таблицы “mutating table”.

Имеются таблицы: Employees (где содержатся сведения о сотрудниках, в частности идентификатор должности и зарплата) и Jobs (где содержатся сведения о должностях, в частности идентификатор должности и минимальная зарплата).

Необходимо создать строчный (не составной) триггер, связанный с таблицей Jobs, который будет вызывать процедуру из пакета EMP_PKG.SET_SALARY(job_id, salary), когда минимальный уровень зарплаты для той или иной должности будет изменяться.

Варианты ниже не работают:
1)

Код


CREATE OR REPLACE TRIGGER upd_minsalary_trg
AFTER UPDATE OF min_salary ON jobs
FOR EACH ROW
WHEN (OLD.min_salary != NEW.min_salary)
BEGIN
    emp_pkg.set_salary(:NEW.job_id, :NEW.min_salary);
END upd_minsalary_trg;

Код


CREATE OR REPLACE TRIGGER upd_minsalary_trg
BEFORE UPDATE OF min_salary ON jobs
FOR EACH ROW
BEGIN
    emp_pkg.set_salary(:NEW.job_id, :NEW.min_salary);
END upd_minsalary_trg;

Я никак не пойму, как создать такой триггер.
Помогите, пожалуйста!

Автор: Akina 6.5.2021, 13:58

Цитата(Rutti @ 5.5.2021, 23:26

)

Варианты ниже не работают:

Расшифруйте эту фразу. Возникает какая-то ошибка (какая? где текст сообщения об ошибке)? С чего Вы вообще решили, что они "не работают"?

Цитата(Rutti @ 5.5.2021, 23:26

)

будет вызывать процедуру из пакета EMP_PKG.SET_SALARY(job_id, salary), когда минимальный уровень зарплаты для той или иной должности будет изменяться.

Ну, допустим, вызвал... как это должно проявиться? какой эффект? как Вы вообще определяете, выполнялась эта процедура или нет?

Автор: Rutti 6.5.2021, 17:54

Цитата(Akina @ 6.5.2021, 13:58

)

Расшифруйте эту фразу.

Была ошибка "ORA-04091: таблица Jobs изменяется, триггер/функция может не заметить это".

Цитата(Akina @ 6.5.2021, 13:58

)

как это должно проявиться? какой эффект?

Должна обновится зарплата в таблице Employees для тех сотрудников, у кого зарплата меньше минимальной в таблице Jobs после выполнения, например, команды

Скрытый текст

Код


UPDATE jobs
SET min_sal = min_sal + 1000
WHERE job_id = 'X';

Задача частично решена. Триггеры работают, но только с использованием флаг-переменной, которая проверяется в триггерах, предназначенных для заполнения локальной индексированной таблицы до того, как начнется модификация данных.

Триггер 1)

Скрытый текст

Код


CREATE OR REPLACE TRIGGER init_jobspkg_trg
BEFORE INSERT OR UPDATE
ON jobs
BEGIN
jobs_pkg.is_init := TRUE;
jobs_pkg.initialize;
END;

Триггер 2)

Скрытый текст

Код


CREATE OR REPLACE TRIGGER after_init_jobspkg_trg
AFTER INSERT OR UPDATE
ON jobs
BEGIN
jobs_pkg.is_init := FALSE;
END;

Триггер 3)

Скрытый текст

Код


CREATE OR REPLACE TRIGGER employee_initjobs_trg
BEFORE INSERT OR UPDATE ON employees
BEGIN
IF jobs_pkg.is_init = FALSE THEN
jobs_pkg.initialize;
END IF;
END;

В связи с этим вопрос немного изменился: можно ли каким-то образом преобразовать Триггер 1) и Триггер 2) в один (не compound) statement триггер BEFORE INSERT OR UPDATE (без введения дополнительных объектов в базу данных)?

Автор: Akina 7.5.2021, 08:42

Цитата(Rutti @ 6.5.2021, 18:54

)

Была ошибка "ORA-04091: таблица Jobs изменяется, триггер/функция может не заметить это".

Что, функция emp_pkg.set_salary вносит изменения в таблицу jobs ???? ну кто так делает! не изменяйте ничего из функций, делайте это в процедуре. Функция должна максимум что возвращать - признак, что требуется обновление, и, возможно, данные для такого обновления...

Цитата(Rutti @ 6.5.2021, 18:54

)

Вообще не понимаю. Что, на Employees тоже висит триггер, который, в свою очередь, обновляет jobs (иначе откуда бы взяться показанной ошибке)? Ну тогда Вы вообще учинили нечто непотребное - кольцевую зависимость в триггерах.

В общем, я бы сказал так - бросайте к чертям собачьим свои затеи с запросами при таком объёме логики на стороне сервера, пока не поздно, и переходите на хранимые процедуры. Заодно и все триггеры можно похерить, а их логику реализовать в тех же процедурах, но вменяемым и корректным способом.

Автор: Rutti 7.5.2021, 13:13

Требуется работать именно в таких условиях.

Есть строчный (не составной) AFTER UPDATE триггер на Jobs, который срабатывает после обновления минимальной з/п в Jobs. В этом триггере вызывается процедура на обновление з/п в таблице Employees. Также есть BEFORE триггер (1) для инициализации локальной индексированной таблицы - копии Jobs (процедура инициализации разработана в пакете).

На таблицу Employees есть триггер check_salary, в котором вызывается процедура для проверки соответствия з/п диапазону [min_sal;max_sal]. При этом также есть BEFORE триггер (2), вызывающий процедуру инициализации локальной индексированной таблицы для Jobs.

Возникает мутация, которая, как я понимаю, связана с тем, что идет обращение к индексированной таблице одновременно и со стороны триггера на Jobs, и со стороны триггера на Employees (но почему?).

Для решения этой проблемы я использую булевскую переменную в том самом пакете, где и процедура инициализации. Значение флага устанавливаю в триггере (1), а потом сбрасываю. В триггере (2) проверяю, что он сброшен. При этом, триггер на Jobs приходится переделать из statement BEFORE INSERT OR UPDATE в составной (добавляю раздел AFTER, где сбрасываю значение булевской переменной). Как-то можно избежать этого составного триггера (желательно без добавления новых объектов в БД)?

Автор: LSD 8.5.2021, 19:12

Это БА такие требования выкатил тригер А и именно строчный, триггер Б и т.д.?

Объясни, что нужно делать-то с точки зрения бизнеса. Отменить изменения, проапдейтить зарплату, письмо бухам отослать.