oracle 基表刷新,oracle trigger是否可以更新基表

在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>