试图限制字符长度时发生的ORA-04098触发器错误

Question

我正试图为我的数据库表创建一个触发器，以便用户只能输入一个长度为6-8个字符的邮政编码。然而，即使触发器没有显示任何错误，这似乎也不起作用。

以下是代码：

create or replace trigger loc_postcode
before insert or update of postcode
on location
for each row
begin
    if ( LENGTH(:new.postcode) > 8) or ( LENGTH(:new.postcode) < 6)
         then    raise_application_error(0001,
             'The postcode must be between 6 and 8 characters long');
         end if;
end;​

而错误是：

ORA-04098:触发器'C3392387.LOC_ID‘无效，重新验证失败

Answer 1

正如其他人提到的，以前版本的触发器存在的问题是，当需要将字符串的长度与值进行比较时，需要将字符串与数字进行比较。我不想再详细谈这件事了。

当前错误的原因是您没有对用户定义的错误使用有效的错误代码。按照文献资料，RAISE_APPLICATION_ERROR过程在-20000到-20999的范围内接受错误代码。将错误代码更改为-20001和扳机会起作用。

我有点惊讶于你为什么会犯这样的错误。我希望您能得到"ORA-21000: error number argument to raise_application_error of 1 is out of range"，就像可以演示的在这个中一样。这可能是因为你在最后一个分号后有一个稍微有点诡异的角色。它在文本编辑器中显示一个十六进制空间，但当我将它复制到SQL时，它可能不会显示出来。这也有可能是Stack Exchange的渲染引擎的伪制品。

顺便说一句，0001不是有效的Oracle代码；00001是唯一的约束冲突，将被声明为-00001 (注意减号)。

然而，我不会这样做。触发器在使用时会产生额外的开销，并且会在数据库中声明模糊约束。使用级联触发器也总是有危险的，这会使您的数据模型非常复杂。

更简单的方法是将您的POSTCODE列声明为最多8个字符/字节(由您决定)，并在该列上添加一个检查约束，以确保邮政编码的长度为6个字符(或更大)。这在表的结构中嵌入了所需的逻辑(因此也包含在Oracle的元数据中)，使得查看所发生的事情更加容易。

如果您要声明您的DDL表如下所示(显然大大简化了)：

create table location ( 
     id number
   , postcode varchar2(8)
   , constraint pk_location primary key (id)
   , constraint ck_location_postcode check (length(postcode) between 6 and 8)
     )

然后，您可以实现相同的结果(工作SQL Fiddle)。注意，列POSTCODE的最大长度为8，这将处理上界，并有一个进一步的检查约束限制它。我定义了check约束来处理上界和下界，这样以后您就可以知道您打算将8作为上界。因此，对列大小的更改不会破坏您的约束。这是一个安全特性，仅此而已，可以在不更改功能的情况下按如下方式声明：

   , constraint ck_location_postcode check (length(postcode) >= 6)

Answer 2

想必，您需要的是长度而不是值：

create or replace trigger loc_id 
before insert or update of postcode
on location
for each row
begin
         if (length(:new.postcode) > 8) or (length(:new.postcode) < 6)
         then    raise_application_error(
             'The postcode must be between 6 and 8 characters long');
         end if;
end;

您的代码不会生成错误，因为Oracle允许您尝试比较字符串和数字。失败是当字符串不是数字格式时。

Answer 3

看起来邮政编码是个字符串。因为您试图检查它的长度，所以需要使用length函数。

if ( LENGTH(:new.postcode) > 8) or ( LENGTH(:new.postcode) < 6)

或者就我个人而言，我宁愿：

if NOT LENGTH(:new.postcode) BETWEEN 6 AND 8 THEN

在您的版本中，您正在尝试将邮政编码的实际值与数字6和8进行比较，这将导致在字符串值无法转换为数字时出现错误。