Как решить ошибку мутации Oracle от TRIGGER

У меня есть две таблицы; TBL_EMPDETAILS (empdetails_id, EMP_SALARY) and TBL_SERVICE (empdetails_id, Salary, Date_Appointed). Идея состоит в том, что когда я обновляю tbl_service (который в основном представляет собой историю зарплат), он должен обновить TBL_EMPDETAILS до самой последней зарплаты.

Я создал TRIGGER, но продолжаю получать MUTATION ERROR. Из моего исследования я видел рекомендуемые составные триггеры, но я не уверен. Я также попробовал прагму автономная_транзакция; перед оператором bgin, но столкнулся с «ОШИБКОЙ ВЗАИМНОЙ БЛОКИРОВКИ»

create or replace trigger Update_Salary  
   before insert or update on "TBL_SERVICE" 
   for each row 
declare
x number ;
y number ;
z date ;
m date;


begin 
x := :NEW."SALARY";
y  := :NEW."EMPDETAILS_ID";
z := :NEW."DATE_APPOINTED";
Select max(DATE_APPOINTED) 
into m From TBL_SERVICE Where Empdetails_id = y ;
IF  z >= m 
THEN
update tbl_empdetails Set EMP_SALARY = x Where Empdetails_id = y ;
End If;
commit;
end;

Я ожидаю, что когда я добавлю строку в TBL_SERVICE, например. (empdetails_id, Salary, Date_Appointed) = (100, 500 долларов США, 20 июля 2019 г.) он должен обновить TBL_EMPDETAILS (empdetails_id, EMP_SALARY) до (100, 500 долларов США)

Ошибка мутации -ORA-04091 Ошибка взаимоблокировки -ORA-00060

Итак, я думаю, что COMPOUND TRIGGER ВЫГЛЯДИТ КАК МАРШРУТ, ЧТОБЫ ИДТИ... Я ПРОБОВАЛ КОД НИЖЕ, НО IM ВСЕ ЕЩЕ ЧТО-ТО ПРОПУСТИЛ :(

create or replace TRIGGER "RDC_HR".Update_Salary  
  FOR UPDATE OR INSERT ON "RDC_HR"."TBL_SERVICE" 
  COMPOUND TRIGGER 

  m date ;

    AFTER EACH ROW IS
     begin 
      Select max(DATE_APPOINTED) into m From TBL_SERVICE 
      Where Empdetails_id = :NEW."EMPDETAILS_ID" ;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
     BEGIN

    IF  (:NEW."DATE_APPOINTED") >= m   THEN
    update tbl_empdetails Set EMP_SALARY = :NEW."SALARY" 
    Where Empdetails_id = :NEW."EMPDETAILS_ID" ;
    End If; 
   END AFTER STATEMENT;

   end Update_Salary;

oracle triggers plsql mutation
person anton_428    schedule 25.07.2019    source источник
comment
Триггеры зло. Переместите логику в процедуру и вызовите ее из своего приложения. Также вы никогда не должны делать коммит в триггере.   -  person OldProgrammer    schedule 25.07.2019
comment
См. ответ здесь: Ссылка   -  person Popeye    schedule 25.07.2019
comment
Есть ли у tbl_empdetails триггер или у tbl_service есть другие триггеры?   -  person gmiley    schedule 25.07.2019
comment
Единственными другими триггерами в обеих таблицах являются триггеры для последовательности идентификаторов.   -  person anton_428    schedule 25.07.2019
comment
Новая версия триггера работать не будет. Проблема в том, что вы не можете просмотреть базовую таблицу в триггере уровня строки. Представьте, что вы выполняете обновление, которое изменяет 50 строк в таблице — Oracle будет запускать триггер на уровне строк пятьдесят раз, но если бы вам было разрешено запрашивать базовую таблицу, вы могли бы увидеть выполненную частичную работу (например, в вашем запросе max(DATE_APPOINTED)), и это было бы двусмысленно. Поэтому Oracle не позволяет вам выполнять запросы к базовой таблице.   -  person Tad Harrison    schedule 29.07.2019

Ответы (2)


arrow_upward
0
arrow_downward

Как насчет merge?

SQL> create table tbl_empdetails (empdetails_id number, emp_salary number);

Table created.

SQL>
SQL> create table tbl_service (empdetails_id number, salary number, date_appointed date);

Table created.

SQL>
SQL> create or replace trigger trg_biu_ser
  2    before insert or update on tbl_service
  3    for each row
  4  begin
  5    merge into tbl_empdetails e
  6      using (select :new.empdetails_id   empdetails_id,
  7                    :new.salary          salary,
  8                    :new.date_appointed  date_appointed,
  9                    (select max(s1.date_appointed)
 10                     from tbl_service s1
 11                     where s1.empdetails_id = :new.empdetails_id
 12                    ) da
 13             from dual
 14            ) x
 15      on (x.empdetails_id = e.empdetails_id)
 16      when     matched then update set e.emp_salary = :new.salary
 17                              where :new.date_appointed > x.da
 18      when not matched then insert (empdetails_id     , emp_salary)
 19                            values (:new.empdetails_id, :new.salary);
 20  end;
 21  /

Trigger created.

SQL>

Тестирование:

SQL> -- initial value
SQL> insert into tbl_service values (1, 100, sysdate);

1 row created.

SQL> -- this is now the highest salary
SQL> insert into tbl_service values (1, 200, sysdate);

1 row created.

SQL> -- this won't be used because date is "yesterday", it isn't the most recent
SQL> insert into tbl_service values (1, 700, sysdate - 1);

1 row created.

SQL> -- this will be used ("tomorrow")
SQL> insert into tbl_service values (1, 10, sysdate + 1);

1 row created.

SQL> -- a new employee
SQL> insert into tbl_service values (2, 2000, sysdate);

1 row created.

SQL>

Окончательный результат:

SQL> select * From tbL_service order by empdetails_id, date_appointed;

EMPDETAILS_ID     SALARY DATE_APPOINTED
------------- ---------- -------------------
            1        700 24.07.2019 15:00:21
            1        100 25.07.2019 15:00:08
            1        200 25.07.2019 15:00:15
            1         10 26.07.2019 15:00:27
            2       2000 25.07.2019 15:00:33

SQL> select * from tbl_empdetails order by empdetails_id;

EMPDETAILS_ID EMP_SALARY
------------- ----------
            1         10
            2       2000

SQL>
person Littlefoot    schedule 25.07.2019

arrow_upward
0
arrow_downward

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

Более серьезная проблема заключается в том, что вы обращаетесь к таблице, для которой был создан триггер, внутри триггера:

Select max(DATE_APPOINTED) 
into m From TBL_SERVICE Where Empdetails_id = y ;

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

Есть несколько способов справиться с этим.

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

Триггер на уровне строки может сообщать о том, что «необходимо выполнить работу», сохраняя состояние в переменной в пакете, а следующий триггер на уровне оператора может затем проверять переменные пакета и выполнять работу на основе содержимого.

Механизм составного триггера — это способ поместить триггеры строки и оператора в одну единицу кода вместе с битами пакета. Это способ написать все это с помощью одного фрагмента кода (составной триггер), а не трех (триггер строки, триггер пакета, триггер оператора).

Вот подробное описание использования составных триггеров: Избавьтесь от ошибок триггера изменяющейся таблицы с помощью составного триггера

Как уже упоминалось, перенос кода из триггеров в хранимую процедуру, безусловно, возможен.

person Tad Harrison    schedule 25.07.2019
comment
Тэд Харрисон, я думаю, что это правильный путь ... но все еще немного не уверен в синтаксисе. - person anton_428; 27.07.2019
comment
См. мою заметку о новой версии триггера. Вы не можете запросить базовую таблицу в триггере уровня строки. Обновление или вставка в базовую таблицу происходит одновременно, поэтому вы можете выполнить запрос MAX только после того, как будет выполнена вся работа на уровне строк. Это можно сделать, сохранив список затронутых первичных ключей в части уровня строки составного триггера, затем просмотрев этот список в части уровня оператора, выполнив постобработку для каждого PK в списке. - person Tad Harrison; 29.07.2019