PLSQL入门与精通（第58章：外键约束错误触发的After行触发器）

本次介绍外键约束错误的时候，触发器执行的情况。
大家都知道，操作数据库的时候，会发生外键约束错误，
如果我们定义了AFTER行触发器的话，改行触发器在发生
外键约束错误也会被触发执行的。这个细节需要注意。

让我们回忆一下，触发器触发触发的过程：
在一个DML文（例UPDATE）中更新多行时
首先在第一行上启动BEFORE的行触发，
接着更新行数据，，
如果该行更新成功，则在该行上启动AFTER的行触发器，
如果该行行触发器执行成功，则再启动下一个行的更新处理。
以上处理反复执行。
如果上述处理过程中，不管是那个阶段发生错误的话，之前启动的触发所进行的处理全部取消。
（但是自治性事物管理的处理不能被取消。）

因此，一般在更新行时发生错误的话，DML语句就会以错误结束，之前启动的所有触发器的处理都会取消。
（当然，自治性事物管理的处理已经提交，不会被取消）

但是，如果发生的错误是外键相关约束的错误的时候，
如果是AFTER的行触发器的话还是会被启动的，直到最后一行更新处理结束。。

但是，即使如果到最后一行为止发生的触发器处理，如果此时没有消除违反外部密钥约束的状态， ◆◆◆◆◆◆
那么最终一系列触发所进行的处理除了自主交易的处理之外，全部被取消，DML的状态将以错误结束。◆◆◆◆◆◆

也就是说，反过来说，即使在外键约束错误发生的情况下，如果在AFTER的行触发器中与之对应，DML语句也能正常处理。◆◆◆◆◆◆

以下是事例：

因为是和外键有关，所以需要2个表：父表和子表。

/************************/

–制作父表（qin表）

/************************/

SQL> CREATE TABLE QIN

2 ( ID NUMBER CONSTRAINT QIN_PK PRIMARY KEY,

3 C1 VARCHAR2(10))

4 /

表创建成功。

/************************/

–子表（ZI）的制作

/************************/

SQL> CREATE TABLE ZI

2 ( ID NUMBER CONSTRAINT ZI_PK PRIMARY KEY,

3 C1 VARCHAR2(10),

4 QIN_ID NUMBER CONSTRAINT ZI_FK REFERENCES QIN（ID）←外键

5 /

这样就可以做成父表（QIN）和子表（ZI）了。
我们插入几条数据。

SQL> INSERT INTO QIN VALUES (1,‘AB’);

创建了一行。

SQL> INSERT INTO QIN VALUES (2,‘CD’);

创建了一行。

SQL> INSERT INTO ZI VALUES (10,‘ab’,1);

创建了一行。

SQL> INSERT INTO ZI VALUES (20,‘cd’,1);

创建了一行。

SQL> INSERT INTO ZI VALUES (30,‘ef’,2);

创建了一行。

现在，父表（QIN）和子表（ZI）有以下数据：

SQL> SELECT * FROM QIN;

ID C1

---

1 AB
2 CD

SQL> SELECT * FROM ZI;

ID C1 OYA_ID

---

10 ab 1
20 cd 1
30 ef 2

我们变更一条数据，发生违反外键约束的错误：

SQL> UPDATE ZI SET QIN_ID = 99 WHERE ID = 10;

UPDATE ZI SET QIN_ID = 99 WHERE ID = 10

行1发生错误。:

ORA-022291：整合性限制（SCOTT.ZI_FK）-没有父关键字

错误解释：我们想把子表的外键设为父表的父键中不存在的值（99），这样就违反了外键约束的（匹配性约束）规则，从而出错。

那么我们就需要做如下处理：
在子表中插入一条数据，或者更新一条数据，如果该数据外键（QIN_ID列）在父表里不存在的话，
就需要在盖父表新增加一条相关的数据。。

/***************************************************************************/

–若父表外键不存在，则触发INSERT触发器

/***************************************************************************/

SQL> CREATE OR REPLACE TRIGGER KO_BEFORE_ROW_TRIG

2 AFTER INSERT OR UPDATE OF QIN_ID

3 ON ZI

4 FOR EACH ROW

5 DECLARE

6 DUMMY CHAR(1);

7 BEGIN

8 SELECT ‘X’ INTO DUMMY FROM QIN WHERE ID = :NEW.QIN_ID;

9 EXCEPTION

10 WHEN NO_DATA_FOUND THEN

11 INSERT INTO QIN VALUES (:NEW.QIN_ID,‘BY TRIGGER’);

12 END;

13 /

触发器已创建。

简单说明一下上述触发器的处理：
首先，第2行～第4行：此触发器是在子表（ZI）中插入数据时处罚的，或者是改子表外键（QIN_ID列）被更新时处罚的，是AFTER的行触发器。
然后，第8行：用INSERT和UPDATE的外键的值（：NEW.QIN_ID）在查询父表是否存的查询处理。查询条件是外键的值（：NEW.QIN_ID）
如果存在，则SELECT INTO语句会返回一行结果，不会发生错误，该触发器不进行任何处理。
如果该SELECT INTO结果为0件，也就是说，如果父键不存在于父表中，则会发生错误（NO_DATA_FOUND例外），执行第11行的INSERT文，在父表中插入一条新的数据。

让我们再试一下之前的更新语句：

SQL> UPDATE ZI SET QIN_ID = 99 WHERE ID = 10;

一行已更新。

这次没有错了。看一下父表数据：

SQL> SELECT * FROM QIN;

ID C1

---

1 AB
2 CD
99 BY TRIGGER ←由子表触发器触发而追加的数据

SQL> SELECT * FROM ZI;

ID C1 QIN_ID

---

10 ab 99 ←UPDATE结果。变更的是外键的值。
20 cd 1
30 ef 2

和我们预期是一样的。
这样，即使将子表外键的值更新为不存在的父键的值，新的父记录也会自动增加而不会出错。

键入我们更新父表，如果发生外键约束错误会怎么样呢？对，和你想的一样，会发生错误。

<想要更新父表中的父键，该键在子表中存在的，所以是不能被变更为其他值。否则会出错 例子如下：

SQL> UPDATE QIN SET ID = 88 WHERE ID = 1;
UPDATE QIN SET ID = 88 WHERE ID = 1

行1发生错误。:

ORA-022292：整合性限制（SCOTT.ZI_FK）-有子记录

确实是发生了外键约束（匹配性约束）的错误。

同理，我们也需要如下触发器：

/***************************************************************************/

–更新父表，父表键在子表中存在的的时候需要触发器触发，同时更新子表的键值

/***************************************************************************/

SQL> CREATE OR REPLACE TRIGGER QIN_AFTER_ROW_TRIG

2 AFTER UPDATE OF ID

3 ON QIN

4 FOR EACH ROW

5 BEGIN

6 UPDATE ZI

7 SET QIN_ID = :NEW.ID

8 WHERE QIN_ID = :OLD.ID;

9 END;

10 /

触发器已创建。

下面简单说明一下上述触发器的处理：
首先，此触发器是在将父表（QIN_ID列）的父键（QIN_ID列）进行UPDATE时启动的，属于AFTER的行触发器。（第2行～第4行）
然后，更新子表的外键：更新值是父表的新的值（：NEW.ID）（第6～8行）。

另外，如果要更新的值在子表记录里不存在的话，这个UPDATE结果是0件，这也是没有问题的。
因为这个UPDATE的DML语句不会出错，这个和SELECT INTO语句不同。

让我们再看一下执行结果：

SQL> UPDATE QIN SET ID = 88 WHERE ID = 1;

UPDATE QIN SET ID = 88 WHERE ID = 1

行1发生错误。:

ORA-04011:表SCOTT.QIN已修改。触发/函数不可见

ORA-06512: "SCOTT.ZI_BEFORE_ROW_TRIG“，行2

ORA-0808：触发’SCOTT.ZI_BEFORE_ROW_TRIG’运行时发生错误

ORA-06512: "SCOTT.QIN_AFTER_ROW_TRIG“，行2

ORA-0808：触发’SCOTT.QIN_AFTER_ROW_TRIG’运行时发生错误

为什么用触发器来对应却发生了错误呢？
因为上述事例违反了在第40章中我们说过的触发器应用规则：“触发器处理的规则（不能做的事情）”
中说明的“在行触发中操作SQL自己的表不能”的规则。
、

但是，有个疑问，刚才的触发器，更新的是子表而不是父表。表面上看SQL操作操作的不是自己的表。
但是，由于父表的更新操作触发了子表的更新操作，子表的更新操作又触发了子表的触发器，
在改触发器中又参考了父表，间接参照自己的表。
也就是说，由于触发器连锁反应，即使转了一圈也会参照原来的父表，这也属于违反了触发规则的内容。

以下是以上例子处理流程：
更新父表⇒启动父表的行触发器⇒更新子表⇒启动子表的行触发器⇒参照父表

如上所述，父表中的行触发器是间接启动的的，但因参考父表的内容从而违反规则发生了错误。

这个例子属于父表和子表的各自行触发器互相更新对方的表，这个错误是不能避免。
（如果不是更新，而是查询（SELECT）的话，可以使用宏包变量等来避免该错误。下次有机会解说）

可以修改如下，让这个触发器无效。

SQL> ALTER TRIGGER ZI_BEFORE_ROW_TRIG DISABLE;

触发器已更改。

然后，再次更新父表的父键。

SQL> UPDATE QIN SET ID = 88 WHERE ID = 1;

一行已更新。

这次没有出错就更新了。数据也参考一下。

SQL> SELECT * FROM QIN;

ID C1

---

88 AB←更新父键的话
2 CD
99 BY TRIGGER

SQL> SELECT * FROM ZI;

ID C1 QIN_ID

---

10 ab 99
20 cd 88←子表的外部按键也更新了
30 ef 2

以上结果，按照我们预想的被更新了。

这样，即使发生外键约束的错误，AFTER的行触发也会异常启动。
利用那个可以实现使用触发器的参照性一致性。