我可以将: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值复制到镜像表中，并用于 :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屋！