用BEFORE触发器修正数据错误

昨天尝试使用BEFORE触发器记录或修正导入过程中出现的数据类型超长的错误，结果没成功，这里研究一下这个问题。

首先建立测试用表：

SQL> CREATE TABLE T_IMP (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T_IMP VALUES (1, 'A');

已创建 1 行。

SQL> INSERT INTO T_IMP VALUES (2, 'B');

已创建 1 行。

SQL> INSERT INTO T_IMP VALUES (1000, 'C');

已创建 1 行。

SQL> INSERT INTO T_IMP VALUES (3, 'D');

已创建 1 行。

SQL> COMMIT;

提交完成。

下面导出数据：

E:>EXP YANGTK/YANGTK FILE=T_IMP.DMP TABLES=T_IMP

Export: Release 9.2.0.4.0 - Production on 星期四 5月 8 23:28:26 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径 ...
. . 正在导出表 T_IMP 4 行被导出在没有警告的情况下成功终止导出。

为了构造错误，下面清空表的数据，并修改表中ID列的精度，使得刚才导出的数据无法成功的导入：

SQL> TRUNCATE TABLE T_IMP;

表被截断。

SQL> ALTER TABLE T_IMP MODIFY ID NUMBER(3);

表已更改。

下面执行导入操作：

E:>IMP YANGTK/YANGTK FILE=T_IMP.DMP TABLES=T_IMP IGNORE=Y

Import: Release 9.2.0.4.0 - Production on 星期四 5月 8 23:30:05 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将YANGTK的对象导入到 YANGTK
. . 正在导入表 "T_IMP"
IMP-00058: 遇到 ORACLE 错误 1438
ORA-01438: 值大于此列指定的允许精确度
IMP-00028: 上一个表的部分导入已回退: 回退 2 行成功终止导入，但出现警告。

错误已经重现，下面可以建立日志表和BEFORE触发器了：

SQL> CREATE TABLE T_IMP_BAK (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE OR REPLACE TRIGGER T_IMP
2 BEFORE INSERT ON T_IMP
3 FOR EACH ROW
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 INSERT INTO T_IMP_BAK VALUES (:NEW.ID, :NEW.NAME);
8 COMMIT;
9 END;
10 /

触发器已创建

建立BEFORE触发器，且设置触发器为自治事务，是为了在错误发生之前，将导致错误产生的数据插入到日志表中，且插入的记录不会随着导入的失败而回滚。

再次执行导入操作：

E:>IMP YANGTK/YANGTK FILE=T_IMP.DMP TABLES=T_IMP IGNORE=Y

Import: Release 9.2.0.4.0 - Production on 星期四 5月 8 23:33:30 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将YANGTK的对象导入到 YANGTK
. . 正在导入表 "T_IMP"
IMP-00058: 遇到 ORACLE 错误 1438
ORA-01438: 值大于此列指定的允许精确度
IMP-00028: 上一个表的部分导入已回退: 回退 2 行成功终止导入，但出现警告。

检查日志表的记录：

SQL> SELECT * FROM T_IMP_BAK;

ID NAME
---------- ------------------------------
1 A
2 B

发现预期的导致错误发生的记录并未写入到日志表中。

导致这种情况出现的可能性有很多中，比如BEFORE触发器并不是想象中的那样真的在插入之前触发；IMP工具的特殊性导致自治事务也被回滚；错误并非发生在插入过程中，而是发生在插入之前；Oracle的bug导致的问题等等。

下面继续验证到底是何种原因导致了当前的问题：

SQL> CREATE OR REPLACE TRIGGER T_IMP
2 BEFORE INSERT ON T_IMP
3 FOR EACH ROW
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 INSERT INTO T_IMP_BAK VALUES (:NEW.ID, :NEW.NAME);
8 COMMIT;
9 IF :NEW.ID > 999 THEN
10 :NEW.ID := 999;
11 END IF;
12 END;
13 /

触发器已创建

SQL> INSERT INTO T_IMP VALUES (1000, 'C');
INSERT INTO T_IMP VALUES (1000, 'C')
*第 1 行出现错误:
ORA-01438: 值大于此列指定的允许精确度

修改触发器，在插入日志后，修改要插入到目标表的值，使得超过精度限制的数据缩小到可以正常插入的范围内，并尝试使用SQL语句来执行插入操作。

结果发现，错误依旧。难道这个错误的产生真的不是在插入的时候，而是在插入之前就会数值进行了判断。

尝试修改列的精度，使得插入数据的原值可以顺利插入：

SQL> ALTER TABLE T_IMP MODIFY ID NUMBER(4);

表已更改。

SQL> INSERT INTO T_IMP VALUES (1000, 'C');

已创建 1 行。

SQL> SELECT * FROM T_IMP;

ID NAME
---------- ------------------------------
999 C

SQL> SELECT * FROM T_IMP_BAK;

ID NAME
---------- ------------------------------
1 A
2 B
1000 C

现在已经可以确定，Oracle会先判断修改的值是否满足表字段的精度设置，然后才会调用BEFORE触发器，接着就是SQL语句本身的执行，最后调用AFTER触发器。

看来利用BEFORE触发器修改超过表字段精度的数据来实现数据正常导入的方式是行不通的。BEFORE触发器的修改只能针对那些本身就满足字段限制的数据。