问题描述
我有一个 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 theREFERENCING
clause of theCREATE 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 存储过程中吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!