Oracle APEX数据库触发器-引用数据库列的问题 [英] Oracle APEX Database Trigger - Problems with referencing database columns

问题描述

我有一个用冒号分隔的值列表，这些值存储在Oracle数据库的varchar2列ORDER_PARTS_LIST中.

I have a colon delimited list of values being stored in a varchar2 column, ORDER_PARTS_LIST, of my Oracle database.

(我知道将数据存储在这样的列表中可能不是最佳做法，但现在只是忽略这一事实.)

以下是相关的表格列:

 ORDER_TABLE(
    ORDER_NUMBER number,
    ORDER_PARTS_LIST varchar(4000))

 PARTS_TABLE(
    PART_NUMBER varchar(20),
    ASSIGNED_ORDER_NUMBER number)

我有条件触发:

 create or replace trigger "ORDER_PARTS_T1"
 BEFORE
 insert or update or delete on "ORDER_TABLE"
 for each row
 begin
   if :new.ORDER_PARTS_LIST LIKE '%'+PART_NUMBER+'%' then
     update PARTS_TABLE set ASSIGNED_ORDER_NUMBER = :ORDER_NUMBER;   
   end if;

 end;

运行此触发器时，出现以下错误:

When I run this trigger I get the following error:

 PLS-00201: identifier 'PART_NUMBER' must be declared

应该发生的事情是，触发器检查PARTS_TABLE中的PART_NUMBERs包含在ORDER_PARTS_LIST中的ORDER_TABLE中，然后为其中的受影响行插入ORDER_NUMBER. PARTS_TABLE的ASSIGNED_ORDER_NUMBER列中的ORDER_TABLE.

What is supposed to happen is that the trigger checks which PART_NUMBERs, in PARTS_TABLE, are included in the ORDER_PARTS_LIST, in the ORDER_TABLE, and then inserts the ORDER_NUMBER, for the affected row in ORDER_TABLE, into the ASSIGNED_ORDER_NUMBER column, of PARTS_TABLE.

最后，应将一个ORDER中的所有PARTS标记为该ORDER的NUMBER.

In the end, all the PARTS in an ORDER should be flagged with that ORDER's NUMBER.

这有意义吗?

我不确定如何在触发器中正确定义变量，以使其运行，说实话，我对触发器是否会执行我认为应该执行的事情存有疑问.像我定义的那样，在获得触发器功能方面的任何建议或帮助都应该很棒.预先感谢.

I am not certain exactly how to properly define the variables in this trigger so that it runs and honestly I have a few doubts as to whether or not the trigger would do what I think it should even if those worked. ANY suggestions or help in getting the trigger functioing like I have defined it should would be great. Thanks in advance.

推荐答案

您可以进行字符串匹配以测试每一行:

You can do string matching to test each row:

create or replace trigger "ORDER_PARTS_T1"
BEFORE
insert or update on "ORDER_TABLE"
for each row
begin
  update PARTS_TABLE p
  set p.ASSIGNED_ORDER_NUMBER = :new.ORDER_NUMBER
  where instr(':' || :new.ORDER_PARTS_LIST || ':'
             ,':' || p.PART_NUMBER || ':') > 0;
end;

例如，如果ORDER_PARTS_LIST为'123:456:789'，则INSTR将找到ID 123、456和789的匹配项，但找不到124、45或8的匹配项.

So for example, if ORDER_PARTS_LIST is '123:456:789', the INSTR will find matches for the ids 123, 456 and 789, but not 124, 45 or 8, for example.

从订单中删除零件时，您将需要一个不同的触发器来NULL PARTS_TABLE中的相应字段:

When parts are removed from an order you will need a different trigger to NULL the appropriate fields in PARTS_TABLE:

create or replace trigger "ORDER_PARTS_T1"
BEFORE
update on "ORDER_TABLE"
for each row
begin
  update PARTS_TABLE p
  set p.ASSIGNED_ORDER_NUMBER = NULL
  where instr(':' || :new.ORDER_PARTS_LIST || ':'
             ,':' || p.PART_NUMBER || ':') = 0
  and instr(':' || :old.ORDER_PARTS_LIST || ':'
             ,':' || p.PART_NUMBER || ':') > 0;
end;

这篇关于Oracle APEX数据库触发器-引用数据库列的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！