我可以将 :OLD 和 :NEW 伪记录复制到 Oracle 存储过程中吗? [英] Can I copy :OLD and :NEW pseudo-records in/to an Oracle stored procedure?

问题描述

我有一个 AFTER INSERT OR UPDATE OR DELETE 触发器，我正在编写它以通过复制 INSERT 和 UPDATE :NEW 值到镜像表中，对于 DELETE，:OLD 值.

I have an AFTER INSERT OR UPDATE OR DELETE trigger that I'm writing to store every record revision that occurs in a certain table, by copying the INSERT and UPDATE :NEW values into a mirror table, and for DELETE the :OLD values.

通过有条件地将 :NEW 或 :OLD 记录传递到一个过程中，然后该过程将插入到我的历史记录表中，我可以大大简化我的代码.不幸的是，我似乎找不到传递整个 :OLD 或 :NEW 记录的方法.

I could un-clutter my code considerably by conditionally passing either the :NEW or :OLD record into a procedure which would then do the insert into my history table. Unfortunately I cannot seem to find a way to pass the entire :OLD or :NEW record.

我是不是遗漏了什么，或者在调用插入过程时没有办法避免枚举每个 :NEW 和 :OLD 列?

Am I missing something or is there no way to avoid enumerating every :NEW and :OLD column as I invoke my insert procedure?

我想做以下事情:

DECLARE
  PROCEDURE LOCAL_INSERT(historyRecord in ACCT.ACCOUNTS%ROWTYPE) IS
  BEGIN
    INSERT INTO ACCT.ACCOUNTS_HISTORY (ID, NAME, DESCRIPTION, DATE) VALUES (historyRecord.ID, historyRecord.NAME, historyRecord.DESCRIPTION, SYSDATE);
  END;
BEGIN
  IF INSERTING OR UPDATING THEN
    LOCAL_INSERT(:NEW);
  ELSE --DELETING
    LOCAL_INSERT(:OLD);
  END IF;
END;

但我坚持这样做:

DECLARE
  PROCEDURE LOCAL_INSERT(id in ACCT.ACCOUNTS.ID%TYPE,
                         name in ACCT.ACCOUNTS.NAME%TYPE,
                         description in ACCT.ACCOUNTS.DESCRIPTION%TYPE) IS
  BEGIN
    INSERT INTO ACCT.ACCOUNTS_HISTORY (ID, NAME, DESCRIPTION, DATE) VALUES (id, name, description, SYSDATE);
  END;
BEGIN
  IF INSERTING OR UPDATING THEN
    LOCAL_INSERT(:NEW.ID, :NEW.NAME, :NEW.DESCRIPTION);
  ELSE --DELETING
    LOCAL_INSERT(:OLD.ID, :OLD.NAME, :OLD.DESCRIPTION);
  END IF;
END;

好的，所以它看起来没有太大区别，但这只是一个包含 3 列而不是几十列的示例.

Okay, so it doesn't look like a big difference, but this is just an example with 3 columns rather than dozens.

推荐答案

不是.你必须自己通过枚举来完成.

It isn't. You have to do it yourself through enumeration.

它不能/不能自动工作的原因包括:

The reasons it can't/doesn't work automatically include:

• :old 和 :new 是默认约定；您可以通过 CREATE TRIGGER 的 REFERENCING 子句将 :old 和 :new 引用命名为您想要的任何内容> 声明.

• the :old and :new are default conventions; you can name the :old and :new references to be whatever you want through the REFERENCING clause of the CREATE TRIGGER statement.

您必须有一个类型的公共声明(通过CREATE TYPE 或通过包声明)才能将其用作另一段代码的参数.

you'd have to have a public declaration of a type (through CREATE TYPE or through a package declaration) to be able to use it as an argument to another piece of code.

触发代码是解释代码，不是编译代码.

trigger code is interpreted code, not compiled code.

这篇关于我可以将 :OLD 和 :NEW 伪记录复制到 Oracle 存储过程中吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！