SQL> Create table tab1 ( YPBH varchar2(10) ,
2 MC varchar2(10) ,
3 SL number(2) ,
4 PFJ number(2) ,
5 PDS number(2));
Table created.
SQL> Insert intotab1 values ( '001' ,'白加黑' , 40, 5, 0);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> Create table tab2 ( XH number(2) ,
2 YPBH varchar2(10) ,
3 MC varchar2(10) ,
4 SL number(2) ,
5 PFJ number(2) ,
6 RKSJ date);
Table created.
SQL>Insert intotab2 values ( 1, '001' ,'白加黑' , 10, 5, to_date('2004-05-10' , 'yyyy-mm-d
;
1 row created.
SQL> Insert intotab2 values ( 2, '001' ,'白加黑' , 15, 5, to_date('2004-05-11' , 'yyyy-mm-dd
1 row created.
SQL> Insert intotab2 values ( 3, '001' ,'白加黑' , 1, 5, to_date('2004-05-12' , 'yyyy-mm-dd'
1 row created.
SQL> Insert intotab2 values ( 4, '001' ,'白加黑' , 8, 5, to_date('2004-05-13' , 'yyyy-mm-dd'
1 row created.
SQL> Insert intotab2 values ( 5, '001' ,'白加黑' , 6, 5, to_date('2004-05-14' , 'yyyy-mm-dd'
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace trigger trg_update_tab1
2after update of PDSon tab1
3for each row
4begin
5update tab2
6 setsl=
7(Select5*no from
8 ( select rowid id , rownum no
9from tab2
10where YPBH = :new.YPBH
11order by RKSJ )A
12where tab2.rowid = id ) ;
13
14
15
16
17end;
18/
Trigger created.
SQL> select xh ,sl from tab2;
XH SL
---------- ----------
1 10
2 15
3
1
4
8
5
6
SQL> update tab1 set pds =1;
1 row updated.
SQL> select xh ,sl from tab2;
XH SL
---------- ----------
1
5
2 10
3 15
4 20
5 25
SQL> commit;
Commit complete. |