在 Oracle 触发器中，我可以将 new 和 old 分配给 rowtype 变量吗? [英] 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 包，通过查询数据字典(即 all_tab_columns 以获取表中的列列表)等操作自动生成您想要的触发代码) ans 使用动态 SQL.根据您预计必须编写的触发器数量，这可能比编写和维护大量类似代码更容易.

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 触发器中，我可以将 new 和 old 分配给 rowtype 变量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！