在Oracle触发器中，可以为行类型变量分配新旧变量吗? [英] In an Oracle trigger, can I assign new and old to a rowtype variable?

问题描述

我的任务是实现多个触发脚本，并且我从同事那里获得了一些示例以供工作.在这种情况下，他们使用pl-sql条件语句进行更新/插入/删除，以及巨大的插入语句(在另一个表中).这些插入语句除了值以new或old前缀之外没有其他变化.我以为我会很聪明，请尝试使用以下方法使我的紧凑一些:

I've been tasked with implementing several trigger scripts, and I've got examples from coworkers to work from. In those, they use pl-sql conditionals for updating/inserting/deleting, along with gigantic insert statements (in another table). These insert statements do not vary other than whether the values are prefixed with new or old. I thought I would be clever and try to make mine a little more compact, using the following:

DECLARE
  vRow SATURN.SPRCMNT%ROWTYPE;

BEGIN

  IF UPDATING THEN
    vRow := :NEW;
  ELSIF INSERTING THEN
    vRow := :NEW;
  ELSIF DELETING THEN
    vRow := :OLD;
  END IF;

  -- Not the real insert statement
  INSERT INTO blah 
    (columns)
    VALUES
    (vRow.somecolumns);

END;

我从编译系统得到了以下信息:

I get the following back from the compile system:

10/13  PLS-00049: bad bind variable 'NEW'
13/13  PLS-00049: bad bind variable 'NEW'
16/13  PLS-00049: bad bind variable 'OLD'

显然，它不喜欢这样.有什么可以实现我正在尝试的东西吗? :new和:old不是真正的行类型吗?将本质上相同的代码重复3次似乎很愚蠢，但是我找不到更好的方法.地狱，我想我读到触发器的大小也有一些硬性限制.任何帮助表示赞赏.

Obviously it does not like this. Is there something that will achieve what I'm attempting? Are :new and :old just not true rowtypes? It seems dumb to repeat what is essentially the same code 3 times over, and yet I can't figure out a better way. Hell, I think I read that there's some hard limit on the size of triggers too. Any help appreciated.

只是想一想，是否可以在DECODE()中以某种方式引用UPDATING/INSERTING关键字?

Just a thought, is it possible to reference the UPDATING/INSERTING keywords somehow within a DECODE()?

例如，如果我可以执行DECODE(SOMETHINGVAR，UPDATING，:NEW.column，DELETING，:OLD.column)，则可以使用单个语句设置vRow中每一列的值.

For instance, if I could do a DECODE(SOMETHINGVAR, UPDATING, :NEW.column, DELETING, :OLD.column) then I could set the value of each column in vRow with a single statement.

我想最好先检查DELETING，然后再将默认值用于其他两个.

I guess it'd be better to check DELETING first, and use the default for the other two.

这可能吗?

推荐答案

不，您不能. :new和:old是伪记录，不是可以分配给局部变量的实际记录.如果您的表基于对象类型，则:new和:old将是特定对象类型的实际实例，可以像其他任何对象一样传递.但是，仅出于使触发器易于编写的目的，就不大可能值得在对象方面定义表.

No, you can't. :new and :old are pseudo-records, not actual records that you can assign to a local variable. If your tables were based on object types, :new and :old would then be actual instances of the particular object type that can be passed around like any other object. But it's pretty unlikely that it is worth defining your tables in terms of objects just to make your triggers easier to write.

您当然可以编写一个PL/SQL程序包，该包可以通过使用动态SQL执行诸如查询数据字典(即all_tab_columns以获取表中列的列表)之类的操作来自动生成所需的触发代码. .根据您预期必须编写的触发器数量，这可能比编写和维护大量相似代码要容易得多.

You could, of course, write a PL/SQL package that automatically generates the trigger code you want by doing things like querying the data dictionary (i.e. all_tab_columns to get the list of columns in a table) ans using dynamic SQL. Depending on the number of triggers you expect to have to write, this may be easier than writing and maintaining a ton of similar code.

这篇关于在Oracle触发器中，可以为行类型变量分配新旧变量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！