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