ITPub博客

flashback table与trigger

原创 Linux操作系统 作者:cqubityj 时间:2007-12-27 10:54:05 0 删除 编辑

flashback table时有一个选项:enable trigger/disable trigger.表示flashback table过程中是否启用trigger,缺省是disable trigger.
如果enable trigger,则flashback table时执行的delete和insert可能会触发trigger,语句执行后trigger仍然有效;
如果disable trigger,则flashback table时执行的delete和insert不会触发trigger,语句执行后trigger仍然有效.
同时flashback table仅做delete和insert,不会恢复trigger.
建议flashback table后最好还是检查一下trigger.

测试过程:
(1) flashback table disable trigger
SQL> create table p (x varchar2(10));

Table created.

SQL> create table t (op varchar2(20),x varchar2(10));

Table created.

SQL> create or replace trigger insert_p
  2  before insert or update or delete on p for each row
  3  begin
  4    if (inserting) then
  5      insert into t values ('insert',:new.x);
  6    end if;
  7    if (deleting) then
  8      insert into t values ('delete',:old.x);
  9    end if;
 10    if (updating) then
 11      insert into t values ('update_before',:old.x);
 12      insert into t values ('update_after',:new.x);
 13    end if;
 14  end;
 15  /

Trigger created.

SQL> insert into p values ('test1');

1 row created.

SQL> insert into p values ('test2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from p;

X
--------------------
test1
test2

SQL> select * from t;

OP                                       X
---------------------------------------- --------------------
insert                                   test1
insert                                   test2

SQL>  select trigger_name,trigger_type,table_name,status from user_triggers where table_name='P';

TRIGGER_NA TRIGGER_TYPE    TABLE_NAME STATUS
---------- --------------- ---------- ----------
INSERT_P   BEFORE EACH ROW P          ENABLED

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 8099976518

SQL> insert into p values ('test3');

1 row created.

SQL> delete from p where x='test1';

1 row deleted.

SQL> update p set x='test20' where x='test2';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from p;

X
--------------------
test20
test3

SQL> select * from t;

OP                                       X
---------------------------------------- --------------------
insert                                   test1
insert                                   test2
insert                                   test3
delete                                   test1
update_before                            test2
update_after                             test20

6 rows selected.

SQL> alter table p enable row movement;

Table altered.

SQL> flashback table p to scn 8099976518;

Flashback complete.

SQL> select * from p;

X
--------------------
test1
test2

SQL> select *from t;

OP                                       X
---------------------------------------- --------------------
insert                                   test1
insert                                   test2
insert                                   test3
delete                                   test1
update_before                            test2
update_after                             test20

6 rows selected.
flashback过程中并未触发trigger.

SQL> select trigger_name,trigger_type,table_name,status from user_triggers where table_name='P';

TRIGGER_NA TRIGGER_TYPE    TABLE_NAME STATUS
---------- --------------- ---------- ----------
INSERT_P   BEFORE EACH ROW P          ENABLED
flashback后trigger仍然有效.

(2) flashback table enable trigger
先删除(1)所建的表,重新创建.
SQL> drop table p purge;

Table dropped.

SQL> drop table t purge;

Table dropped.

SQL> truncate table sys_temp_fbt;

Table truncated.

SQL> drop table sys_temp_fbt purge;

Table dropped.

SQL> create table p (x varchar2(10));

Table created.

SQL> create table t (op varchar2(20),x varchar2(10));

Table created.

SQL> create or replace trigger insert_p                 
  2  before insert or update or delete on p for each row
  3  begin                                              
  4    if (inserting) then                              
  5      insert into t values ('insert',:new.x);        
  6    end if;                                          
  7    if (deleting) then                               
  8      insert into t values ('delete',:old.x);        
  9    end if;                                          
 10    if (updating) then                               
 11      insert into t values ('update_before',:old.x); 
 12      insert into t values ('update_after',:new.x);  
 13    end if;                                          
 14  end;                                               
 15  /                                                  

Trigger created.

SQL> insert into p values ('test1');

1 row created.

SQL> insert into p values ('test2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from p;

X
--------------------
test1
test2

SQL> select * from t;

OP                                       X
---------------------------------------- --------------------
insert                                   test1
insert                                   test2

SQL> select trigger_name,trigger_type,table_name,status from user_triggers where table_name='P';

TRIGGER_NA TRIGGER_TYPE    TABLE_NAME STATUS
---------- --------------- ---------- ----------
INSERT_P   BEFORE EACH ROW P          ENABLED

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 8099977371

SQL> insert into p values ('test3');

1 row created.

SQL> delete from p where x='test1';

1 row deleted.

SQL> update p set x='test20' where x='test2';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from p;

X
--------------------
test20
test3

SQL> select * from t;

OP                                       X
---------------------------------------- --------------------
insert                                   test1
insert                                   test2
insert                                   test3
delete                                   test1
update_before                            test2
update_after                             test20

6 rows selected.

SQL> alter table p enable row movement;

Table altered.

SQL> flashback table p to scn 8099977371 enable trigger;

Flashback complete.

SQL> select * from p;

X
--------------------
test1
test2
表已经flashback.
SQL> select * from t;

OP                                       X
---------------------------------------- --------------------
insert                                   test1
insert                                   test2
insert                                   test3
delete                                   test1
update_before                            test2
update_after                             test20
delete                                   test20
delete                                   test3
insert                                   test1
insert                                   test2

10 rows selected.
flashback时触发了trigger,因此表中多出后4条记录.这4条记录反映了flashback过程中执行的DML语句.

SQL> select * from sys_temp_fbt;                                                                 

SCHEMA     OBJECT_NAM    OBJECT# RID                  AC
---------- ---------- ---------- -------------------- --
USER1      P               63800 AAAPk4AAWAAAAAdAAB   D
USER1      P               63800 AAAPk4AAWAAAAAdAAC   D
USER1      P               63800 AAAPk4AAWAAAAAdAAA   I
USER1      P               63800 AAAPk4AAWAAAAAdAAB   I
t表中的后4条记录与sys_temp_ftb表中的4条记录相对应.


SQL> select trigger_name,trigger_type,table_name,status from user_triggers where table_name='P';

TRIGGER_NA TRIGGER_TYPE    TABLE_NAME STATUS
---------- --------------- ---------- ----------
INSERT_P   BEFORE EACH ROW P          ENABLED
trigger仍然有效.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/228190/viewspace-6676/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-19

  • 博文量
    133
  • 访问量
    423248