mysql数据库级联删除父子表的sql 数据库 级联删除

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

) 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!!!