*第 1 行出现错误:
# o2 f) R0 k6 d1 T ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志4 Q' m) j0 R4 E
下面构造一个触发器:
" L$ N; X9 {: v- y) t r SQL> CREATE OR REPLACE TRIGGER T_P_SET_DEFAULT
0 |/ s4 U6 D* N8 J' W' s3 J6 P 2 BEFORE DELETE OR UPDATE OF ID ON T_P
0 J4 L( J, Q) ]% k: Z0 f 3 FOR EACH ROW
& ^2 ?/ h# ?5 O& v" f5 `, R2 ] 4 WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))& F0 P; u) T) I; L+ U
5 DECLARE
0 ~7 R% z& b, z6 ^ 6 V_STR VARCHAR2(32767);1 z% t4 |6 i! r
7 BEGIN6 ?8 w2 g* U! H X6 w
8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME ( j7 W0 _" q- @8 i1 S/ V% r% q
9 FROM ALL_CONSTRAINTS
5 s2 h: P& S. v* G. D* h 10 WHERE CONSTRAINT_TYPE = ’R’
. u& A. a2 _* W: |$ | V5 l$ |" _- f 11 AND R_OWNER = ’YANGTK’
; E$ ^2 X* \6 i2 f) v( V+ R! { 12 AND R_CONSTRAINT_NAME IN
' i; Q# ^- m9 O: f 13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
+ q% r, v3 n) \7 G2 n3 [ 14 LOOP
) a/ W: h6 B: m8 p A& Q 15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’; ) L, L/ Z" s, t& `# \2 t
16 FOR J IN (SELECT A.COLUMN_NAME, B.DATA_DEFAULT) G, i/ t! B: S7 E
17 FROM ALL_CONS_COLUMNS A, ALL_TAB_COLUMNS B( y3 j5 W; C! }1 G/ Q* B9 i- U4 j' \
18 WHERE A.OWNER = I.OWNER
" q* G+ Y6 a4 m 19 AND B.OWNER = I.OWNER
; [+ V/ @8 l% Q+ w& m+ V 20 AND A.TABLE_NAME = I.TABLE_NAME/ y3 B+ n) l" \* P6 k0 b" g) R
21 AND B.TABLE_NAME = I.TABLE_NAME
- m; p2 c' T) B+ @, d 22 AND A.COLUMN_NAME = B.COLUMN_NAME
& P7 }0 J/ ^- k6 ?% L, a6 r1 y 23 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
2 }) @, ?1 T( X% _5 o. _! V) s" p5 M. W 24 LOOP6 [5 s% U+ P. v8 p8 q+ P
25 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || J.DATA_DEFAULT * B' K" E! t- r3 g) y7 ]
26 || ’ WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;9 p: Y9 @ O4 I# y, h
27 END LOOP;
% g0 s' `5 a$ [) | 28 EXECUTE IMMEDIATE V_STR;
% X; T# {" s% ]2 }" z: W* Z 29 END LOOP;- b0 N& X+ C; L3 b( {# D/ ^8 i
30 END;
# R+ r4 Y, E2 m% q 31 /
; F5 \& b; w+ I) v# P& _ 触发器已创建6 [- y" { ~1 i
SQL> ALTER TABLE T_C MODIFY FID DEFAULT 2;
p0 G9 R0 e; V! l7 t1 `/ } 表已更改。
7 o2 a; [# T- R: b9 Z8 m SQL> SELECT * FROM T_P;( P, [* F1 h+ ]* G# \8 t
ID NAME
! i, ^ e0 J) f# \: P( k5 z1 k9 @( a ---------- ------------------------------
. X0 b' @# E. D- f/ u 1 A4 i* I H0 {' ]2 w4 v- r1 W3 U
2 B
: @7 e. w. X5 N8 L- ~ SQL> SELECT * FROM T_C;3 e6 {% ~0 d* V8 D! ?1 h1 g
ID FID NAME
; @ x& l% O0 F1 |1 A! ?" i* \ ---------- ---------- ------------------------------
& |1 T5 w6 I( {! b7 g 1 1 A( R2 v& N& `6 w$ {# B8 a9 K3 b
SQL> UPDATE T_P SET ID = 3 WHERE ID = 1; F6 H' @+ G5 B4 K5 j) V
已更新 1 行。) p$ Y; |1 f% m2 c
SQL> SELECT * FROM T_P;+ {% j" w l" ~3 h( }
ID NAME
- M/ N2 [) u. Q ---------- ------------------------------( {* @5 e! \' e" ?
3 A
& q9 O, G( P. V! f9 v 2 B
% }! X& R* V z% d; S) x. \ SQL> SELECT * FROM T_C;( e% x9 V& x0 l, R6 m) H
ID FID NAME3 N+ c) S7 N1 I2 J4 I5 x
---------- ---------- ------------------------------9 y6 ^8 P, ?/ X& J
1 2 A
& e! b$ D2 X5 ?! Z* B9 `7 v 下面检查一下DELETE操作是否有效:
9 m# Z- r% v, P& r* V! y& @; ?6 H SQL> UPDATE T_C SET FID = 3;7 B$ l; y+ [5 N* U- z0 ^
已更新 1 行。! e! p% h* P( I2 { }9 R9 m$ A! v
SQL> SELECT * FROM T_C;
4 J5 N% K! d* ]5 B4 U! d ID FID NAME5 t# K% n7 ?4 v' A0 |
---------- ---------- -----------------------------4 Y/ Y& F1 K# f% i' [
1 3 A
3 S. W. a9 l- p& A3 z SQL> DELETE T_P WHERE ID = 3;4 x% B4 O: A4 k7 ~3 e/ o" N, `2 L
已删除 1 行。
! S) l0 v1 I8 {1 @+ R( k5 E! c SQL> SELECT * FROM T_P;
) M9 f+ d9 y) V/ a# ? ID NAME3 [8 g! B3 t' p$ @9 A
---------- ------------------------------% S4 u' J! b8 C! D
2 B
( I. x3 H* A- r& r4 ]4 K! W' X SQL> SELECT * FROM T_C;) m' Z* t5 M. x, S
ID FID NAME l$ ?) ~7 k; K& w
---------- ---------- -----------------------------
5 n9 @, l5 M: u, X$ Z A6 U 1 2 A |