对于NO ACTION来说,约束的检查发生在语句之后,所有上面的语句可以顺利执行,而对于RESTRICT而言,这个操作会直接报错。& y4 w) L* t7 T, n
下面构建一个RESTRICT操作的触发器:
+ l; x/ s' _$ A M SQL> CREATE OR REPLACE TRIGGER T_P_RESTRICT. l$ V$ W, H0 r7 t
2 BEFORE DELETE OR UPDATE OF ID ON T_P
. L( M( [" ]5 T3 X/ f* D& r 3 FOR EACH ROW" \7 d7 Q: s# I. v6 g
4 WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))
& t; P7 g9 t2 Y* _ 5 DECLARE C* S1 ?/ b) y9 c( {
6 V_STR VARCHAR2(32767);
# c% l3 L7 S5 J9 x 7 V_COUNT NUMBER;
6 ]8 N- _9 G' V" [4 ? 8 BEGIN
6 D, j8 w! C% k% f; c 9 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME 6 I8 q: \, p; G! t/ I# ~7 g; S
10 FROM ALL_CONSTRAINTS( w8 o; z( [/ S* X7 D
11 WHERE CONSTRAINT_TYPE = ’R’
k3 S- h7 h* u+ m: o- O 12 AND R_OWNER = ’YANGTK’9 O) P/ L8 b$ V# N) O
13 AND R_CONSTRAINT_NAME IN
/ Y8 T: Z `5 h' O5 u 14 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’)): j% ]/ r( z, g; p9 _* N
15 LOOP4 |' \8 a, w4 x8 p
16 V_STR := ’SELECT COUNT(*) FROM ’ || I.TABLE_NAME || ’ WHERE ’;
+ r, ~$ Z. r; q- I0 R6 r 17 FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
9 w2 |3 |, m& _% @ 18 WHERE OWNER = I.OWNER
; a' i* q# t8 d2 N5 {1 C 19 AND TABLE_NAME = I.TABLE_NAME
& }5 ?# ]& ~$ U/ q1 q2 O8 ~7 @ 20 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME) 5 U4 w7 w' G1 r3 P9 {2 d I
21 LOOP
3 Z4 J ^9 z4 j( Z/ b( ] 22 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
6 n+ ^; ?% @0 G 23 END LOOP;
* R8 ^: \" X8 D P( Q 24 EXECUTE IMMEDIATE V_STR INTO V_COUNT;3 b, m8 J+ Q. j5 ~* M a. L
25 IF V_COUNT > 0 THEN* g1 o5 r( H' C
26 RAISE_APPLICATION_ERROR(-20001, ’违反完整约束条件限制操作 - 已找到子记录日志’);
. g& S% ]- G% a4 Z% T2 | 27 END IF;$ r2 U7 `3 f- B! l5 T
28 END LOOP;1 C! n `+ _# l( g! _+ Q7 g% M
29 END;
2 ?. ]. l0 F4 s9 r& @ 30 /
. t1 M9 @! Q/ X# r! {% b( t 触发器已创建
H J! L5 s0 d5 w! l/ ^" f( X SQL> SELECT * FROM T_P;
) h7 t# B. P% }- U! @& z5 h. M: _ ID NAME2 y+ v# E8 y+ l* }! S! S
---------- ------------------------------6 R' c% _2 C! @, r
1 A
/ C, s. z8 j/ q; C& G" U 2 B
+ `3 [! ~: N8 c8 A SQL> SELECT * FROM T_C;
! X [6 [8 O) v2 T! L2 r% E9 ^; Y ID FID NAME" j A! k4 |$ i0 _
---------- ---------- ------------------------------; @6 w% t+ Y( ^# H; ?
1 1 A1 O3 P# [' P+ }: U# x# S; E% {
SQL> UPDATE T_P SET ID = ID - 1;6 I$ A+ | z' E, A) A' Z
UPDATE T_P SET ID = ID - 1* B( }; j# O! V- W8 n% I2 A$ u- v
*第 1 行出现错误:$ G* V" D* W! `0 t! c
ORA-20001: 违反完整约束条件限制操作 - 已找到子记录日志
& X8 _3 Z( c- c3 V: W* w* f ORA-06512: 在"YANGTK.T_P_RESTRICT", line 22
6 I# B2 o, Y, e ORA-04088: 触发器 ’YANGTK.T_P_RESTRICT’ 执行过程中出错2 k. K2 X. [ w& j
这种方式可以实现RESTRICT操作,但是对于其他会话的外键的引用,RESTRICT操作是看不到的:+ \# J# d+ ?7 U& w( `3 o7 B
SQL> CONN YANGTK/YANGTK@YTK92已连接。
/ x. @; S: B' t5 u$ F4 R0 ^ SQL> SET SQLP ’SQL2> ’ q7 n7 `+ E( O$ q+ p
SQL2> INSERT INTO T_C VALUES (2, 2, ’B’);
9 {, f; l- r2 J9 l$ @6 n 已创建 1 行。. p; f7 Q3 P9 [7 G5 ^1 e1 j5 I
在另一个会话插入一条参考主表ID为2的记录,且不提交,这时在主表更新或删除ID为2的记录时,会被锁住,RESTRICT操作不会起作用,因为这时看不到其他用户未提交的修改:
4 a4 _* a+ B: b8 D' M SQL> DELETE T_P WHERE ID = 2;+ y6 Y4 c3 _$ g' `: S8 N
而如果在第二个会话中提交事务:
: \; d% I6 T# e6 T SQL2> COMMIT;
& s% L$ ?5 W6 s& V l1 K 提交完成。- {& ]6 J4 s ?* G
则RESTRICT操作会起作用,因为这时已经提交,而当前的会话是可以看到其他会话中已提交数据的:* h0 ^( A# I* X) E( }8 x/ b
DELETE T_P WHERE ID = 2' b$ \1 S; ?# j* M+ l( C8 H
*第 1 行出现错误:* ?/ h) _" C5 p/ M1 J) t* v
ORA-20001: 违反完整约束条件限制操作 - 已找到子记录日志3 W& P" d, b$ |: u9 I2 B- E
ORA-06512: 在"YANGTK.T_P_RESTRICT", line 22
& s1 l8 @3 [$ \3 d8 r0 t, J ORA-04088: 触发器 ’YANGTK.T_P_RESTRICT’ 执行过程中出错' e2 A4 ~$ D% a( c: F# T) h
SQL> |