BEFORE触发器修正数据错误

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

获取导致导入失败的数据http://yangtingkun.itpub.net/post/468/461401

首先建立测试用表：

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: Release9.2.0.4.0 - Production on星期四5月8 23:28:26 2008

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

?oJQM$u0连接到: Oracle9i Enterprise Edition Release9.2.0.4.0 - ProductionITPUB个人空间!s k*Q#O y$yx"H
With the Partitioning, OLAP and Oracle Data Mining options
iP$Gw/}0JServer Release 9.2.0.4.0 - ProductionITPUB个人空间(IZ(xb"V-R*~8i
已导出ZHS16GBK字符集和AL16UTF16 NCHAR字符集

即将导出指定的表通过常规路径...ITPUB个人空间U p bKl'T0OJ;l
. .正在导出表                           T_IMP          4行被导出
+Z2h}IgOW0在没有警告的情况下成功终止导出。

为了构造错误，下面清空表的数据，并修改表中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: Release9.2.0.4.0 - Production on星期四5月8 23:30:05 2008

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

ITPUB个人空间.So7SS`FEEk
连接到: Oracle9i Enterprise Edition Release9.2.0.4.0 - ProductionITPUB个人空间x:Zu+L;xa@
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间%a[ U;MW2La$P
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件
serF3v4pV0已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入ITPUB个人空间gou]Dde
.正在将YANGTK的对象导入到YANGTKITPUB个人空间Fi#v"zr"p
. .正在导入表                         "T_IMP"
+?2~-P5tJ0IMP-00058:遇到ORACLE错误1438ITPUB个人空间^:yp9]2CZ"~m!xS
ORA-01438:值大于此列指定的允许精确度
:uJ&sr!o,mr0IMP-00028:上一个表的部分导入已回退:回退2行
V(th/u/m[-i`S'X-?0成功终止导入，但出现警告。

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

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

表已创建。

SQL> CREATE OR REPLACE TRIGGER T_IMPITPUB个人空间-G.ZEj^#?~
  2  BEFORE INSERT ON T_IMP
%]3U'Vm F8C
f:Z0  3  FOR EACH ROWITPUB个人空间3Z8k(}E |^S
{%V#g"M
  4  DECLAREITPUB个人空间vw8X6OO1i6{+\G
  5   PRAGMA AUTONOMOUS_TRANSACTION;ITPUB个人空间Z KGK8{-sB6X!n+e;_
  6  BEGINITPUB个人空间R0^$Gej(_C;[e)v;Q j
  7   INSERT INTO T_IMP_BAK VALUES (:NEW.ID, :NEW.NAME);ITPUB个人空间Rp \Gh
  8   COMMIT;ITPUB个人空间W.E;m"n,n'LH(x
  9  END;ITPUB个人空间2C"~6cC0O&OgC3G;V@
 10  /

触发器已创建

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

再次执行导入操作：

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

Import: Release9.2.0.4.0 - Production on星期四5月8 23:33:30 2008

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

ITPUB个人空间x_cow,`8b o
连接到: Oracle9i Enterprise Edition Release9.2.0.4.0 - ProductionITPUB个人空间3V _0r:`ld#bQ PM
With the Partitioning, OLAP and Oracle Data Mining options
b Z8z&\&hc&?q:BY0JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件ITPUB个人空间 KY$_y
X8_3Q2a&\7?
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入
T7We'Y1]}0.正在将YANGTK的对象导入到YANGTKITPUB个人空间 jh'j/|/o$Kk\v
. .正在导入表                         "T_IMP"
Ww^AoTaj*r0IMP-00058:遇到ORACLE错误1438
:u^`o+Qc@7W0ORA-01438:值大于此列指定的允许精确度ITPUB个人空间9Klz8@hM"w
IMP-00028:上一个表的部分导入已回退:回退2行ITPUB个人空间$_}+Xq'@2i#[)K3k
成功终止导入，但出现警告。

检查日志表的记录：

SQL> SELECT * FROM T_IMP_BAK;

        ID NAME
pw5Wt*J`0---------- ------------------------------ITPUB个人空间&jVL N[7I/`.N
        1 AITPUB个人空间pV*X0Y#H
         2 B

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

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

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

SQL> CREATE OR REPLACE TRIGGER T_IMP
&y9t*]t"}/em0  2  BEFORE INSERT ON T_IMP
a9`+\2sU9H0  3  FOR EACH ROWITPUB个人空间0I(W-TA:f
  4  DECLAREITPUB个人空间?1G9?Ak|.S
  5   PRAGMA AUTONOMOUS_TRANSACTION;ITPUB个人空间6Y {5_Arnjt
  6  BEGINITPUB个人空间m N+V5n7Bt6yl+Ms L
  7   INSERT INTO T_IMP_BAK VALUES (:NEW.ID, :NEW.NAME);
~Uitp%D0K&_.[+b0  8   COMMIT;
N0`f5z@ L*lWc(}0  9   IF :NEW.ID > 999 THENITPUB个人空间U2Mg_B3r7z
 10    :NEW.ID := 999;
$q:H&A Q7w0 11   END IF;
*Pz+MGFG~6A0 12  END;
$G"]2E.OyF&B0 13  /

触发器已创建

SQL> INSERT INTO T_IMP VALUES (1000, 'C');
3|/\6g R9UZe"]'Xn0INSERT INTO T_IMP VALUES (1000, 'C')
Jl-i7y
n*l C/OuL}0                          *ITPUB个人空间[,?2k|x-i.?ab
第1行出现错误:
H)CV!]\0ORA-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
"q.Q8Sk(g~9`0---------- ------------------------------
X.^'Lu#l2NK[m0      999 C

SQL> SELECT * FROM T_IMP_BAK;

        ID NAME
+CF0j[7e0---------- ------------------------------
~X~6uC*E]p%XWW"{0        1 AITPUB个人空间8\a{;h/v$S"WR9Q.U
         2 BITPUB个人空间6ZPhM'HX(l
     1000 C

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

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