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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/228190/viewspace-6676/