sql - :OLD and :NEW variables in a trigger? 属于什么数据类型

Question

假设您在 MY_CUSTOMER_TABLE 上有一个触发器并且它有一个声明为 MY_CUSTOMER_TABLE%ROWTYPE 类型的变量.我如何分配 OLD值到那个变量？

CREATE TRIGGER CUSTOMER_BEFORE
  BEFORE UPDATE ON MY_CUSTOMER_TABLE
  FOR EACH ROW
DECLARE
  old_version MY_CUSTOMER_TABLE%ROWTYPE;
BEGIN
  old_version := :OLD; /* Causes a PLS-00049 bad bind variable 'OLD' */
  old_version := OLD;  /* Causes a PLS-00201 identifier 'OLD' must be declared */
END;

编辑:

澄清一下，这是因为我使用触发器来归档来自 MY_CUSTOMER_TABLE 的行。进入 MY_CUSTOMER_TABLE_HISTORY .根据正在执行的操作( INSERT 、 UPDATE 、 DELETE )，我需要来自 OLD 的所有字段或 NEW :

CREATE TRIGGER CUSTOMER_BEFORE
  BEFORE UPDATE ON MY_CUSTOMER_TABLE
  FOR EACH ROW
DECLARE
  historical_record MY_CUSTOMER_TABLE_HISTORY%ROWTYPE;

  PROCEDURE
    copy
    (
      source_record      MY_CUSTOMER_TABLE%ROWTYPE,
      destination_record IN OUT MY_CUSTOMER_TABLE_HISTORY%ROWTYPE
    )
  BEGIN
    destination_record.customer_id   := source_record.customer_id;
    destination_record.first_name    := source_record.first_name;
    destination_record.last_name     := source_record.last_name;
    destination_record.date_of_birth := source_record.date_of_birth;
  END;

BEGIN
  /* I didn't want to replicate the same assignment statements for 
     each of the two cases: */
  CASE
    WHEN INSERT OR UPDATING THEN
      copy( source_record => :NEW, destination_record => historical_record );

    WHEN DELETING THEN
      copy( source_record => :OLD, destination_record => historical_record );

  END CASE;

  /* Some other assignments to historical_record fields... */

  INSERT INTO MY_CUSTOMER_TABLE_HISTORY VALUES historical_record;
END;

在这种情况下，PL/SQL 不会让我通过 :OLD或 :NEW到期望 MY_CUSTOMER_TABLE%ROWTYPE 的过程争论。

Answer 1

你不能。
引用所有列(如 SELECT *)通常是不好的做法，您应该指定所需的列。