我可以将 :OLD 和 :NEW 伪记录复制到 Oracle 存储过程中吗？

Question

我有一个 AFTER INSERT OR UPDATE OR DELETE 触发器，我正在编写该触发器，通过复制 INSERT 和 UPDATE 来存储某个表中发生的每个记录修订 :NEW 值放入镜像表中，DELETE 则放入 :OLD 值。

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

我是否遗漏了某些内容，或者在调用插入过程时是否无法避免枚举每个 :NEW 和 :OLD 列？

我想做以下事情：

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 列而不是数十列的示例。

Answer 1

事实并非如此。你必须自己通过枚举来完成。

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

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

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

• 触发代码是解释代码，而不是编译代码。

Answer 2

我认为这是不可能的。 文档没有提到类似的内容。

这肯定会降低性能，但您可以尝试定义触发器AFTER INSERT和另一个BEFORE UPDATE OR DELETE，并在触发器中执行类似以下操作：

SELECT *
INTO rowtype_variable
FROM accounts
WHERE accounts.id = :NEW.id; -- :OLD.id for UPDATE and DELETE

然后调用您的触发器使用该 rowtype_variable 的过程。

Answer 3

使用SQL生成SQL；

select ' row_field.'||COLUMN_NAME||' := :new.'||COLUMN_NAME||';'  from 
    ALL_TAB_COLUMNS cols 
where 
    cols.TABLE_NAME = 'yourTableName'
order by cols.column_name.

然后复制并粘贴输出。

Answer 4

如果您使用AFTER触发器，您可以使用rowid作为参数来调用过程

insert into t_hist
select * from t where rowid = r;

如果您使用BEFORE触发器，您将得到ORA-04091 变异表，但是您的解决方案可以是（http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm)：

• 不要使用触发器 - 避免变异表错误的最佳方法是不使用触发器。虽然面向对象的 Oracle 提供了与表关联的“方法”，但大多数精明的 PL/SQL 开发人员都会避免使用触发器，除非绝对必要。
• 使用“after”或“instead of”触发器 - 如果必须使用触发器，最好通过使用“after”触发器来避免变异表错误，从而避免与变异表相关的货币问题。例如，使用触发器“:after update on xxx”，原始更新已完成，表不会发生变化。
• 重新设计触发器语法 - Hall 博士对改变表错误有一些很好的注释，并提供了其他方法来避免通过行级和语句级触发器的组合来改变表。
• 使用自治事务 - 您可以通过将触发器标记为自治事务来避免变异表错误，使其独立于调用该过程的表。