oracle 集联删除,[转载]oracle的多表之间的级联删除

最近软件系统中要删除一条记录,就要关联到同时删除好多张表,他们之间还存在着约束关系.所以考虑到在创建表时加上约束关系,具体如下:

) ORACLE则只充许级联删除B4 I$ Z* T+ # b6

|

SQL级联删除与级联更新使用格式:

CREATE TABLE A001(ID INT PRIMARY KEY，NAME

VARCHAR(20))5 T'

CREATE TABLE A002(ID INT REFERENCES

A001(ID)ON DELETE CASCADE ON UPDATE CASCADE，AGE TINYINT)

ORACLE级联删除使用格式：

CREATE TABLE A001(ID INT PRIMAY KEY，NAME

VARCHAR2(20))) {- P3 T' J3 [+

l

CREATE TABLE A002(ID INT REFERENCES A001(ID)ON DELETE CASCADE，AGE

NUMBER(2，0))

SQL与ORACLE的外键约束--级联删除

最近软件系统中要删除一条记录,就要关联到同时删除好多张表,他们之间还存在着约束关系.所以考虑到在创建表时加上约束关系,具体如下:(

?% P3 |3 d" u' |

! F% `- }

Q7 ?3 t" t. n! d. X

SQL的外键约束可以实现级联删除与级联更新;0 f'

ORACLE则只充许级联删除。

SQL级联删除与级联更新使用格式：

CREATE TABLE A001(ID INT PRIMARY KEY，NAME

VARCHAR(20))

CREATE TABLE A002(ID INT REFERENCES

A001(ID)ON DELETE CASCADE ON UPDATE CASCADE，AGE

TINYINT)6

u! L* a/

ORACLE级联删除使用格式：

CREATE TABLE A001(ID INT PRIMAY KEY，NAME

VARCHAR2(20))

CREATE TABLE A002(ID INT REFERENCES

A001(ID)ON DELETE CASCADE，AGE NUMBER(2，0))

--------------

CREATE TABLE

groups

(

id VARCHAR2(16) CONSTRAINT pk_groupid

PRIMARY KEY, name VARCHAR2(32), / V9 S3 w4

q1

description VARCHAR2(50) 2

R# )

)

TABLESPACE userspace;

CREATE TABLE usringrp

(

group_id VARCHAR2(16) CONSTRAINT

fk_uing_grpid

REFERENCES groups(id)

ON DELETE CASCADE,

user_id VARCHAR2(16)

)

TABLESPACE userspace;

--------------- * y/ M! p

PowerDesigner

参照完整性约束

限制(Restrict)。不允许进行修改或删除操作。若修改或删除主表的主键时，如果子表中存在子记录，系统将产生一个错误提示。这是缺省的参照完整性设置。

置空(Set Null)。如果外键列允许为空，若修改或删除主表的主键时，把子表中参照的外键列设置为空值(NULL)。

置为缺省(Set

Default)。如果指定了缺省值，若修改或删除主表的主键时，把子表中参照的外键设置为缺省值(Default)。 H级联(Cascade)。把主表中主键修改为一个新的值时，相应修改子表中外键的值；或者删除主表中主键的记录时，要相应删除子表中外键的记录。

----------------------------------------------------------------------------------------------

我平时比较少用触发器，主要是因为程序逻辑不对的时候不容易发现错误，有时数据量大了也可能产生性能上的问题，但这个东西总有用武之地，在很多场合还是会起到巨大的作用。

这两天就遇到一个问题，有两张表的一个字段需要进行同步更新，也就是A表修改时要把对应的B表的记录8 p.

M& f3 H4 `( z1 T" u) @1 d. Q; N

字段修改，反过来B表修改时也要把A表的修改，保持两边数据的一个同步，这个可以在前台很容易的实现，但开发

人员不想修改代码了，就考虑在后台用trigger实现。

功能很简单，但在实现时遇到一个问题，就是A上的DML触发了上面的TRIGGER，然后这个TRIGGER去更新B表，这样

就会触发B表上的触发器，而B表上的TRIGGER又会更新A表，这样就迭代触发，没有结束了，也就是会产生变异表(mutating);

O" D2 D) L/ {8 o' m

我不知道ORACLE的触发器是否有属性来限制这种情况的发生，但以前做SQL SERVER时知道有种Instaed

of的触发器，他表示

当DML启动他后，他将以TRIGGER里的代码来代替这个DML动作，也就是DML不会真正的执行，只会启动INSTEAD

TRIGGER，最终

执行的是TRIGGER里面的编码。

查看了Docs，看到ORACLE也支持这个类型的触发器，但这个只能建立到视图上，不能基于表建立，我要的功能肯定是可以8

N+ D% v; M$ l/ w" D

实现的，在这里我把原表进行了rename，引如了两张视图，名字就是以前的表名，这样对于他们前台应用就做了个6 I _- I- U. n2 f% z4

q

透明的切换，然后在两个视图上建立INSTEAD触发器，将任何两个视图上的更新都传播到后面的两个基表，这样不管你更新那个:

G* R5 b, L: T% }

视图，我都可以捕获到数据，以代码在后面更新，也不存在互相触发，因为触发器修改的对象已经转移到表了，而此时表上是没有'

E$ G/ T% b% Y; _/ Z; M! b3 ?

trigger的，呵呵！！！

过程如下/ {$

F) M8 p7 }$ s

--创建测试表

SQL> create table

mytest1(row_num number,row_name varchar2(50));2 d

表被创建

SQL> create table

mytest2(row_num number,row_name varchar2(50));

表被创建5 e;

--测试数据* `0

SQL> INSERT INTO MYTEST1

VALUES(1,'Fhhh!!!');1 i6 u

1 行 已插入

SQL> INSERT INTO MYTEST2

VALUES(1,'Watch your mouth!!!');/ G0 C0]]

1 行 已插入7 l9 t

SQL> COMMIT;/ ~4 }2 ^

提交完成

--先在一个表上创建触发器! 8 d'

v%

SQL> CREATE OR REPLACE

TRIGGER TRI_TEST1+ {( S- c,

i)

2 BEFORE UPDATE

3 ON MYTEST1* ?- y! m2 z$ a. H. s0

C3 L& l8

4 FOR EACH ROW

5 DECLARE

6 lv_new VARCHAR2(20);

7 lv_parent VARCHAR2(20);

8 BEGIN

9 lv_new := :new.row_name;%

10 lv_parent := :OLD.row_name;7

11 IF lv_new

<> lv_parent THEN ^7

12 UPDATE MYTEST23 o, J4 B9 m3 `9

m

13 SET ROW_NAME = :NEW.ROW_NAME

14 WHERE ROW_NUM = :NEW.ROW_NUM;4 ?5 h; ?

z

15 END IF;. }! L# @' e8 S: Y)

K

16 DBMS_OUTPUT.PUT_LINE(lv_new ||

lv_parent);

17 END;# f8

触发器被创建

--测试更新

SQL> set serveroutput

on

SQL> UPDATE MYTEST1 SET

ROW_NAME = 'DO it!!!';

DO it!!! Fhhhh!!!: Y- b$ W, p% A" {5 F/

q

1 行 已更新

--更新成功( v: i2 w+ L; `;

V

SQL> SELECT * FROM

MYTEST2;

ROW_NUM ROW_NAME

----------

--------------------------------------------------

1 DO

it!!!9 M-

a' k0 B+ v* C* `3 H; ]

--另外张表创建触发器

SQL> CREATE OR REPLACE TRIGGER TRI_TEST2

0 O% G-

n, u! B- a

2 BEFORE UPDATE "

3 ON MYTEST2 9 [) q+

}.

4 FOR EACH ROW

5 DECLARE

6 lv_new VARCHAR2(20); ) A2 K( n*

i%

7 lv_parent VARCHAR2(20);

8 BEGIN & h1

s' J3 S# Y2 ?0 G, Z'

9 lv_new := :new.row_name;

10 lv_parent := :OLD.row_name; % W) r' N/ M5 h#

}

11 IF lv_new

<> lv_parent THEN

12 UPDATE MYTEST1

13 SET ROW_NAME = :NEW.ROW_NAME

14 WHERE ROW_NUM = :NEW.ROW_NUM;

15 END IF; +

16 DBMS_OUTPUT.PUT_LINE(lv_new ||

lv_parent);

17 END;

18 /

--产生了变异表，更新失败0 m: T' B% l/ a0 i# {/

~) Y'

SQL> update mytest1 set

row_name = 'mouthkkkkkoo';

update mytest1 set row_name =

'mouthkkkkkoo'

ORA-04091: table MYTEST1 is mutating,

trigger/function may not see it

ORA-06512: at "TRI_TEST2", line

89 ?'

[(

ORA-04088: error during execution of trigger

'TRI_TEST2'&

G& M8 y8 | t* R& M

ORA-06512: at "TRI_TEST1", line 8

ORA-04088: error during execution of trigger

'TRI_TEST1'

--更新失败7 w,

s& t+

SQL> update mytest2 set

row_name = 'mouthkkkkkoo';+ H! r. i8

G& ( r2 W N3 [

update mytest2 set row_name = 'mouthkkkkkoo'- A7 @7 z0 X) X-

x

ORA-04091: table MYTEST2 is mutating,

trigger/function may not see it9 |.

ORA-06512: at "TRI_TEST1", line 8

ORA-04088: error during execution of trigger

'TRI_TEST1',

m8 i)

ORA-06512: at "TRI_TEST2", line

8) |3 i-

r @3 f4 _, w7 [6 c

ORA-04088: error during execution of trigger

'TRI_TEST2'

--删除触发器$ U: ^+ d# U, I) a%

R2

SQL> drop trigger

TRI_TEST2;

触发器被删掉% d# t- b5

}*

SQL> drop trigger

TRI_TEST1;

触发器被删掉

--创建视图

SQL> CREATE VIEW V_TEST1 AS

SELECT * FROM MYTEST1;6 O9 q! }* $ T#

~

(

视图被创建; D' y#

t& F-

SQL> CREATE VIEW V_TEST2 AS

SELECT * FROM MYTEST2;

视图被创建

--基于视图创建Instead触发器$ j1 s;

D& Q2 {1 l. L& r! X

SQL> CREATE OR REPLACE

TRIGGER TRI_TEST2) p5

v& ]+ Y+ v) C8 Q- |/ Y

2 INSTEAD OF UPDATE

3 ON V_TEST26 R! k

4 FOR EACH ROW

5 DECLARE

6 lv_new VARCHAR2(20);

7 lv_parent VARCHAR2(20);( i7 C/ Y( E1

g

8 BEGIN

9 lv_new := :new.row_name;

10 lv_parent := :OLD.row_name;) G$ |.

11 IF lv_new

<> lv_parent THEN

12 UPDATE MYTEST2/ B5 R

13 SET ROW_NAME = :NEW.ROW_NAME# o! R, k7 i3 u) V)

B!

14 WHERE ROW_NUM = :NEW.ROW_NUM;- _)

15 UPDATE MYTEST1% u" V7 p6 Y5 y ~7

d

16 SET ROW_NAME = :NEW.ROW_NAME M& z5

N) R% d- {

17 WHERE ROW_NUM = :NEW.ROW_NUM;

18 END IF;) i4 J |+ A X) _1 t;

X

19 DBMS_OUTPUT.PUT_LINE(lv_new ||

lv_parent);% E$ A6 l! u. a8 e%

?

20 END；

21、

触发器被创建

--功能已经实现2 f* h5

j& @0 ]1

SQL> update v_test1 set

row_name = 'I with you!!!';

1 行 已更新 -

SQL> commit; & i4

H* g: x) G+ b- D& ~3 i. x

提交完成 6 ~8

?& C6 u6

SQL> select * from v_test2;

% Q Q! U$

d/ o" X#

ROW_NUM ROW_NAME

----------

--------------------------------------------------( n% n.

t#

1 I with you!!!

SQL> update v_test2 set row_name = 'Don't me!!!';

: C/ U( ~:

v. [4

SQL> commit; ; m5 D)

SQL> select * from v_test1;

/

k& T7 M7 S4 K

ROW_NUM ROW_NAME

0 [3 {/ S$

N% H

----------

--------------------------------------------------

1 Don't me!!!