Oracle自动在多记录块的第2部分中插入记录 [英] Oracle automatically insert record in multirecord block part 2

问题描述

我的桌子看起来像这样:

My table looks like this:

+-------------------+
|Name               |
+-------------------+
|Name1              |
|Name2              |
|Name3              |
|Name4              |
|Name1Jr            |
|Name2Jr            |
|Name4Jr            |
+-------------------+

我的多行块看起来像:

我想知道的是，在插入名称后，如何插入与Jr同名的记录.例如，我插入了Name2，它也会将Name2Jr插入到multirow块中.像这样:

What I wanted to know is how can I insert a record that has the same name with Jr after I insert a name. For example, I inserted Name2, it will also insert Name2Jr into the multirow block. Like this:

注意: :我需要从数据库中获取自动插入的数据的值.

Note: I need to get the value of the automatically inserted data from database.

我尝试过 When-NEW-RECORD-INSTANCE 触发器(@Littlefoot先生回答我的最后一个问题):

I tried in WHEN-NEW-RECORD-INSTANCE trigger(the answer of Sir @Littlefoot on my last question):

if :system.trigger_record = 1 and :test.name is null then
    -- do nothing if it is the first record in a form
    null;
else
    duplicate_record;
    if substr(:test.name, -2) = 'Jr' then
        -- you've duplicated a record which already has 'Jr' at the end - don't do it
        :test.name := null;
    else       
        -- concatenate 'Jr' to the duplicated record
        :test.name := :test.name || 'Jr';
    end if;      
end if;  

现在，我想知道是否有办法在 WHEN-VALIDATE-RECORD 触发器上执行此操作.问题在于 duplicate_record 不能在 WHEN-VALIDATE-RECORD 触发器中使用.如何使用过程，函数或其他东西来做到这一点?预先感谢！

And now, what I want is to know if there is a way to do it on WHEN-VALIDATE-RECORD trigger. The problem there is that the duplicate_record can't be used in WHEN-VALIDATE-RECORD trigger. How to do it using procedure, function or something? Thanks in advance!

推荐答案

DUPLICATE_RECORD是受限制的过程，您不能在WHEN-VALIDATE-RECORD触发器(或任何其他同类的触发器)中使用它.

DUPLICATE_RECORD is a restricted procedure and you can't use it in WHEN-VALIDATE-RECORD trigger (or any other of the same kind).

由于您必须导航到下一条记录(如果要复制它)，即使将那个受限制的过程放入另一个PL/SQL程序单元中，所有内容也会传播-最终-引发相同的错误.所以...你不走运.

As you have to navigate to the next record (if you want to copy it), even if you put that restricted procedure into another PL/SQL program unit, everything will just propagate and - ultimately - raise the same error. So ... you're out of luck.

即使您编写了一个(存储的)过程，该过程会将"Jr"行插入到后台的数据库中，您也必须将这些值提取到屏幕上.由于EXECUTE_QUERY是执行此操作的方法，并且(又是)另一个受限制的过程，因此也不起作用.

Even if you wrote a (stored) procedure which would insert that "Jr" row into the database somewhere behind the scene, you'd have to fetch those values to the screen. As EXECUTE_QUERY is the way to do it, and as it is (yet another) restricted procedure, that won't work either.

如果计划清除数据块并手动填充(使用循环)，则必须使用NEXT_RECORD导航到下一个(下一个和下一个)记录，这又是一个受限制的过程.此外，如果它是一个数据块(是的，是的)，那么在保存更改后，您实际上将为所有记录创建重复项，所以-要么它会因违反唯一约束而失败(很好)，否则您将创建重复项(更糟).

If you planned to clear data block and fill it manually (by using a loop), you'd have to navigate to next (and next, and next) record with NEXT_RECORD, and that's again a restricted procedure. Besides, if it was a data block (and yes, it is), you'd actually create duplicates for all records once you'd save changes so - either it would fail with unique constraint violation (which is good), or you'd create duplicates (which is worse).

顺便说一句，WHEN-NEW-RECORD-INSTANCE有什么问题?使用时有什么问题?

BTW what's wrong with WHEN-NEW-RECORD-INSTANCE? What problems do you have when using it?

这篇关于Oracle自动在多记录块的第2部分中插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！