*第 1 行出现错误:
5 M' k+ |& C- D& T ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志% M! f9 l! O) B+ w; D& l" m" w( t
下面构造一个触发器:
7 Z; @, i1 Q; | SQL> CREATE OR REPLACE TRIGGER T_P_SET_DEFAULT
7 A' U3 j" e8 w; h( {( P 2 BEFORE DELETE OR UPDATE OF ID ON T_P
( a! x# |$ F) G! c" c 3 FOR EACH ROW- P; O! W* L, N8 R
4 WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))
% k+ x; f9 v1 s4 h 5 DECLARE! e% R) @, D) v9 U5 c5 I
6 V_STR VARCHAR2(32767);
( O& i4 i: s6 |7 E 7 BEGIN% Y. @) {. B5 S3 _5 S
8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
& f" Z- ?( M* o0 b1 u' T, r 9 FROM ALL_CONSTRAINTS
" [$ x7 S/ W( B5 B 10 WHERE CONSTRAINT_TYPE = ’R’
6 W+ |5 Y9 t N8 I" S2 @ 11 AND R_OWNER = ’YANGTK’
0 h6 B$ G6 V5 A7 C3 q4 N 12 AND R_CONSTRAINT_NAME IN
/ `5 T& m& q% q! n, H0 `6 F 13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))5 I4 i9 H" N3 }) \& f: ]6 R
14 LOOP
+ P1 f. r* K6 k 15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’; $ o% Y" I& c1 ~: E) [
16 FOR J IN (SELECT A.COLUMN_NAME, B.DATA_DEFAULT
/ ?! L+ R" x( b 17 FROM ALL_CONS_COLUMNS A, ALL_TAB_COLUMNS B
7 @0 @6 |# U- g7 G) `1 P 18 WHERE A.OWNER = I.OWNER/ o* u# t- v% i: z$ L. R
19 AND B.OWNER = I.OWNER" B9 k. U4 ~$ T/ O
20 AND A.TABLE_NAME = I.TABLE_NAME& c. A) T6 d$ _% o+ `3 `3 B' f
21 AND B.TABLE_NAME = I.TABLE_NAME
! c3 s* X9 n$ R4 ] `& V 22 AND A.COLUMN_NAME = B.COLUMN_NAME
# P+ c& i. [; y( x3 Z 23 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME) . C9 ~2 Z. Q! w8 N7 Q: U
24 LOOP
6 j0 Q' e) p+ j* ~2 _+ l: s 25 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || J.DATA_DEFAULT
$ W. N! }* z# n5 _& o7 ~; C 26 || ’ WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
, w4 k! \2 M) G/ s 27 END LOOP;# u, A" Z8 v4 O1 D3 I
28 EXECUTE IMMEDIATE V_STR;, i) _! {2 F( P0 o* N. W
29 END LOOP;) ~6 M! ?' i6 [; V5 ]- |4 c" k
30 END;
) a: [3 [- j. S: j# ]5 N6 N3 d 31 /
3 L) X! Y+ L* c; x 触发器已创建
9 F+ e4 S3 t- M$ D3 C# O: m SQL> ALTER TABLE T_C MODIFY FID DEFAULT 2;
! V+ _! a# E) o: [0 B! A 表已更改。
5 c4 a$ r& P( N+ k5 U SQL> SELECT * FROM T_P;# b, ~& g* [ W2 }7 `6 y* Z
ID NAME
. }4 b/ Y6 G. L- ~1 r) t" c ---------- ------------------------------2 {, H3 g1 S+ ]
1 A1 I5 J6 I8 X4 ] d7 o! C; k
2 B
; c' k/ R$ u7 f, R6 l. g- d o SQL> SELECT * FROM T_C;: N" ]1 c5 c/ B3 T0 V
ID FID NAME
2 X y: t' i% r% C$ Z ---------- ---------- ------------------------------3 |( q. m8 ~( n5 ?) X
1 1 A, x9 W% w0 ~5 |5 B
SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
; T2 K. ?) i5 }! C 已更新 1 行。* H1 J" d% X3 ^( G
SQL> SELECT * FROM T_P;
5 S1 `: n$ V+ K2 P( B$ K3 P ID NAME9 p9 y5 e& U6 ?- ]$ P$ m3 N
---------- ------------------------------
6 C; ]9 h/ B6 Q7 w" _6 u% v; o 3 A
0 a1 z, N/ e r0 O" E, m2 q 2 B$ n) V( x, S; [5 Z) H$ `
SQL> SELECT * FROM T_C;) u' F# O" o4 [. l0 h
ID FID NAME
) w' }- K4 i7 {; G6 p8 C0 l ---------- ---------- ------------------------------6 @/ s7 A- S- G4 b& z
1 2 A
% ?' r! c- f p" |! w 下面检查一下DELETE操作是否有效:
4 a& F3 _7 j" j8 ?5 g! Y' X$ T+ H3 E SQL> UPDATE T_C SET FID = 3;
* H0 m- K% y6 k+ j/ \/ l( w' S 已更新 1 行。
1 b1 I2 ]: \" Y SQL> SELECT * FROM T_C;4 z. ^1 G: ^1 w) P; J$ L. m
ID FID NAME
) B' J3 ^+ M0 e% \5 T/ O7 Z. ~ ---------- ---------- -----------------------------
% A& Y4 }# `. k: | }1 I/ A* c 1 3 A
d. P2 Y: N& a R4 ? SQL> DELETE T_P WHERE ID = 3;& Q) R; f/ v! e' X% s: e
已删除 1 行。
2 {& m# v) ^( x# _6 Q SQL> SELECT * FROM T_P;
$ m( m; X3 `% `- h' T0 b ID NAME1 ]& \$ o* U' h
---------- ------------------------------
( s/ X* [1 U, u) Q P) _" h% K 2 B
$ V3 F9 K8 P3 ~) o5 B' T7 _3 C/ p SQL> SELECT * FROM T_C;
& U3 G, U9 S" v1 C: p- ] ID FID NAME
# U5 G p$ T6 [! I8 b ---------- ---------- -----------------------------
2 U8 S* V5 a% x 1 2 A |