oracle 触发器 merge,如何从触发器中解决Oracle变异错误

怎么样

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>