*第 1 行出现错误:
4 Y9 F- R' Q2 I. \" Z7 [( ? ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志( s2 A! A. d# c* z* I) |$ ]
下面构造一个触发器:
4 D( |, q C) Q" g5 F SQL> CREATE OR REPLACE TRIGGER T_P_SET_DEFAULT 9 |* H$ G( y: Q+ N$ y2 }/ y
2 BEFORE DELETE OR UPDATE OF ID ON T_P5 t& v% R& H2 I; H
3 FOR EACH ROW
1 L* A: j7 S( ~; k7 u3 ~ 4 WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))9 a9 d1 F. F4 E+ |* a$ a7 T7 _
5 DECLARE! L& L' l! k9 M4 ]! Q
6 V_STR VARCHAR2(32767);3 ~7 R2 L- K" b! O! ]
7 BEGIN" n. ~! y5 h( O# Q c8 i- N, u
8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME $ o0 H' G5 X6 Q0 c& ^# ]4 U
9 FROM ALL_CONSTRAINTS; R D6 V6 \ U4 ?: r& @
10 WHERE CONSTRAINT_TYPE = ’R’
: R0 G7 I# n& }$ A/ K" u 11 AND R_OWNER = ’YANGTK’; O& s E- A- K6 a7 T1 D9 y
12 AND R_CONSTRAINT_NAME IN # Q& M1 b# \( W5 k# c# U1 I7 a
13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
. S. n+ W, e3 u2 @ 14 LOOP
5 h# B) H% D* L1 }2 E0 B 15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’; ; D( s( j. h1 j, s" Q5 o0 o2 ^, a1 o B
16 FOR J IN (SELECT A.COLUMN_NAME, B.DATA_DEFAULT- c9 J: B, t% _( W
17 FROM ALL_CONS_COLUMNS A, ALL_TAB_COLUMNS B
0 R7 @5 ]- |" W1 k 18 WHERE A.OWNER = I.OWNER' ?7 w/ v" Q5 C/ u, D7 f
19 AND B.OWNER = I.OWNER
% e7 o8 r D5 |5 ]" J. O 20 AND A.TABLE_NAME = I.TABLE_NAME
3 [0 L, s4 ?, [2 l 21 AND B.TABLE_NAME = I.TABLE_NAME : b) c) v% C# h6 j1 [0 r0 l
22 AND A.COLUMN_NAME = B.COLUMN_NAME' J- K/ K: f( b4 o: L
23 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME) ( O: P2 q* Y6 ?1 f" M' ^ M# H
24 LOOP' ^: ]. P; m# H" c+ i6 c2 y$ ?
25 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || J.DATA_DEFAULT ! c; H2 Y8 a0 i: j- E) d9 r* p
26 || ’ WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
8 C- }% x# o2 s% U' E- B 27 END LOOP;9 o: p; i0 ~1 L" W
28 EXECUTE IMMEDIATE V_STR;
1 Z6 d0 K/ y6 r0 W* Y$ B 29 END LOOP;
1 |! y2 U9 H, R, J! I/ ? 30 END;" E% J, G0 p- N$ \+ m$ w- O
31 /
6 M9 F7 N2 s' {- h1 R( u 触发器已创建
- ?5 E5 Y% a' u0 V: ? SQL> ALTER TABLE T_C MODIFY FID DEFAULT 2;
4 J2 l5 Q* k) ^' v* g7 f# X 表已更改。
4 `+ M, t( ^9 ?5 S% D SQL> SELECT * FROM T_P;
( E8 n, v+ @& z0 ~/ m( M D9 c) D ID NAME
+ O6 u8 S, ~2 \) k9 y- Y7 q1 z8 u ---------- ------------------------------
/ g( D: A9 q P6 j& }7 i; w 1 A
3 x7 F+ _0 x3 P4 ^0 [ 2 B' v0 V$ x. N! k4 t% h( w" z% ]
SQL> SELECT * FROM T_C;( {- r$ b& i4 M7 T7 v
ID FID NAME
% G2 S3 m' z9 W: I# _. T# w8 W ---------- ---------- ------------------------------
$ B4 F; a5 t% b0 t6 |; r8 F* m2 ` 1 1 A
3 G/ }) L1 U- o0 ~* O7 | SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;, M$ z* Q4 R. \
已更新 1 行。% _' V$ {$ x; B& w6 J% {: t' e
SQL> SELECT * FROM T_P;
% h; O" `; f4 u9 v& F& ^5 q ID NAME
Q- {: g* \4 y* `5 G4 A! G( g ---------- ------------------------------
; U$ W0 i! o7 P( Z3 C 3 A1 |, g: U+ V2 ?, ^0 j% ^
2 B/ y* ~& }* K- G, c
SQL> SELECT * FROM T_C;9 O+ z5 ?+ ~( V3 l, P8 f
ID FID NAME
W! B% N# A! @6 U6 y9 Q ---------- ---------- ------------------------------
3 D' q) _0 Q/ T2 x* V8 n& |' }. D 1 2 A7 I/ T( b9 l2 u5 o
下面检查一下DELETE操作是否有效:5 P1 t k5 b5 ? l: v
SQL> UPDATE T_C SET FID = 3;
( Y3 Q) b) A/ b& r2 f5 p- v3 b1 ^ 已更新 1 行。" @4 m1 }" |! c- B. D
SQL> SELECT * FROM T_C;
- p' X& ^4 W r; }( w2 X( b ID FID NAME
2 Y: z9 R/ }2 G/ x# T' P9 W ---------- ---------- -----------------------------
' d9 B2 H- G. D. D: r2 F 1 3 A
- p8 o' G. W, c: z* n SQL> DELETE T_P WHERE ID = 3;
& S- J7 ?$ M# s4 ^ 已删除 1 行。
5 N' _3 U/ s" J( n9 \/ j SQL> SELECT * FROM T_P;8 ~6 L3 q( n3 R( N! r8 y* W
ID NAME
: {. R, _) ^* g/ f4 f( u- h" t8 X ---------- ------------------------------
; m; N# u5 f5 h( c5 O 2 B
' W( |# o8 r# o2 _0 G% i SQL> SELECT * FROM T_C;* c' x2 O+ B2 r5 E" @
ID FID NAME
- p3 E K; l+ |1 I- H5 m' w B- X ---------- ---------- -----------------------------$ i" z! b2 G! N* e
1 2 A |