小议Oracle外键约束修改行为(6)
前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE.至于SQL标准中定义的其他操作，Oracle只能通过触发器来实现，这里给出一个简单的SET DEFAULT操作的例子。
SQL> DROP TABLE T_C;
表已删除。
SQL> DROP TABLE T_P;
表已删除。
SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
表已更改。
SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
2 FOREIGN KEY (FID)
3 REFERENCES T_P (ID);
表已更改。
SQL> INSERT INTO T_P VALUES (1, ’A’);
已创建 1 行。
SQL> INSERT INTO T_P VALUES (2, ’B’);
已创建 1 行。
SQL> INSERT INTO T_C VALUES (1, 1, ’A’);
已创建 1 行。
SQL> COMMIT;
提交完成。
检查一下直接更新操作：
SQL> select * FROM T_P;
ID NAME
---------- ------------------------------
1 A
2 B
SQL> select * FROM T_C;
ID FID NAME
---------- ---------- ------------------------------
1 1 A
SQL> update T_P SET ID = 3 WHERE ID = 1;
UPDATE T_P SET ID = 3 WHERE ID = 1
*第 1 行出现错误:
ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志
下面构造一个触发器：
SQL> CREATE or REPLACE TRIGGER T_P_SET_DEFAULT
2 BEFORE DELETE OR update OF ID ON T_P
3 FOR EACH ROW
4 WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))
5 DECLARE
6 V_STR VARCHAR2(32767);
7 BEGIN
8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
9 FROM ALL_CONSTRAINTS
10 WHERE CONSTRAINT_TYPE = ’R’
11 AND R_OWNER = ’YANGTK’
12 AND R_CONSTRAINT_NAME IN
13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
14 LOOP
15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’;
16 FOR J IN (SELECT A.COLUMN_NAME, B.DATA_DEFAULT
17 FROM ALL_CONS_COLUMNS A, ALL_TAB_COLUMNS B
18 WHERE A.OWNER = I.OWNER
19 AND B.OWNER = I.OWNER
20 AND A.TABLE_NAME = I.TABLE_NAME
21 AND B.TABLE_NAME = I.TABLE_NAME
22 AND A.COLUMN_NAME = B.COLUMN_NAME
23 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
24 LOOP
25 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || J.DATA_DEFAULT
26 || ’ WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
27 END LOOP;
28 EXECUTE IMMEDIATE V_STR;
29 END LOOP;
30 END;
31 /
触发器已创建
SQL> ALTER TABLE T_C MODIFY FID DEFAULT 2;
表已更改。
SQL> select * FROM T_P;
ID NAME
---------- ------------------------------
1 A
2 B
SQL> select * FROM T_C;
ID FID NAME
---------- ---------- ------------------------------
1 1 A
SQL> update T_P SET ID = 3 WHERE ID = 1;
已更新 1 行。
SQL> select * FROM T_P;
ID NAME
---------- ------------------------------
3 A
2 B
SQL> select * FROM T_C;
ID FID NAME
---------- ---------- ------------------------------
1 2 A
下面检查一下DELETE操作是否有效：
SQL> update T_C SET FID = 3;
已更新 1 行。
SQL> select * FROM T_C;
ID FID NAME
---------- ---------- -----------------------------
1 3 A
SQL> DELETE T_P WHERE ID = 3;
已删除 1 行。
SQL> select * FROM T_P;
ID NAME
---------- ------------------------------
2 B
SQL> select * FROM T_C;
ID FID NAME
---------- ---------- -----------------------------
1 2 A
- 小议Oracle外键约束修改行为(6)
- › 小议Oracle外键约束修改行为(7)
- › 小议Oracle外键约束修改行为(6)
- › 小议Oracle外键约束修改行为(5)
- › 小议Oracle外键约束修改行为(4)
- › 小议Oracle外键约束修改行为(3)
- › 小议Oracle外键约束修改行为(2)
- › 小议Oracle外键约束修改行为(1)
- 在百度中搜索相关文章：小议Oracle外键约束修改行为(6)
- 在谷歌中搜索相关文章：小议Oracle外键约束修改行为(6)
- 在soso中搜索相关文章：小议Oracle外键约束修改行为(6)
- 在搜狗中搜索相关文章：小议Oracle外键约束修改行为(6)
tag: ORACLE/CIW认证，ciw认证,ciw认证考试,oracle认证，考试频道 - 计算机考试 - ORACLE/CIW认证