[20130218]修改主键和触发器.txt

[20130218]修改主键和触发器.txt

http://connormcdonald.wordpress.com/2012/01/22/updating-primary-keys-and-triggers/

重复试验,加强理解.

1.测试1:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


create table T ( x number primary key, y date );
insert into T values (1,sysdate);
insert into T values (2,sysdate);
insert into T values (3,sysdate);
insert into T values (4,sysdate);
commit ;
create or replace view V as select * from T;
create or replace
trigger TRIG instead of update on V
for each row
begin
  update T
  set x = :new.x
  where x = :old.x;
end;
/

update T set x = x + 1;
roll;

SQL> update V set x = x + 1;
update V set x = x + 1
       *
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0036534) violated
ORA-06512: at "SCOTT.TRIG", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG'

--如果禁止触发器,没有问题.
SQL> alter trigger trig disable ;
Trigger altered.

SQL> update V set x = x + 1;
4 rows updated.

SQL> roll;
Rollback complete.

2.测试2(存在主外键的情况):
create table PARENT ( p number primary key );
create table CHILD ( f_p number constraint FK references PARENT );
insert into PARENT values (1);
insert into PARENT values (2);
insert into PARENT values (3);
insert into CHILD  values (1);
insert into CHILD  values (2);
insert into CHILD  values (3);
commit ;


create or replace
trigger TRIG1 after update on PARENT
for each row
begin
  update CHILD set f_p = :new.p where f_p = :old.p;
end;
/

--修改单行记录:
SQL> UPDATE PARENT SET p = 4 where p=1;
1 row updated.

SQL> select * from parent;

         P
----------
         2
         3
         4

SQL> select * from child;

       F_P
----------
         4
         2
         3
SQL> roll ;
Rollback complete.
        
--没有问题.


SQL> UPDATE PARENT SET p = p+1;
3 rows updated.

SQL> select * from parent;

         P
----------
         2
         3
         4

SQL> select * from child;

       F_P
----------
         4
         4
         4

SQL> roll ;
Rollback complete.

--噢,问题严重,child的f_p全部变成了4.

总结:
    作为dba维护系统最讨厌的就是触发器.一般讲做一些改动的时候最好先检查一下该表上是否存在触发器.