oracle - 我如何从 TRIGGER 解决 Oracle 突变错误

Question

我有两张桌子；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-Jul-2019) 它应该将 TBL_EMPDETAILS (empdetails_id, EMP_SALARY) 更新为 (100, $500)

突变错误 -ORA-04091 死锁错误 -ORA-00060

所以我认为复合触发器看起来像要走的路线......我尝试了下面的代码，但我仍然遗漏了一些东西:(

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;

标签： oracleplsqltriggersmutation

Answer 1

怎么样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>