在Oracle 11g中,借助compound trigger,可以轻松的解决类似的问题,比如:
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as study
SQL>
SQL> create table zrp (no int);
Table created
SQL> create or replace trigger trig_comp_zrp
2 for insert on zrp
3 compound trigger
4
5 v_no number;
6
7 after each row is
8 begin
9 v_no:=:new.no;
10 end after each row;
11
12 after statement is
13 begin
14 update zrp set no=v_no+100 where no=v_no;
15 end after statement;
16 end;
17 /
Trigger created
SQL> select * from zrp;
NO
---------------------------------------
SQL> insert into zrp values(1);
1 row inserted
SQL> select * from zrp;
NO
---------------------------------------
101
SQL> insert into zrp values(2);
1 row inserted
SQL> select * from zrp;
NO
---------------------------------------
101
102
SQL> insert into zrp values(3);
1 row inserted
SQL> select * from zrp;
NO
---------------------------------------
101
102
103
SQL> commit;
Commit complete
SQL> select * from zrp;
NO
---------------------------------------
101
102
103
SQL>
