Of what data type are the :OLD and :NEW variables in a trigger?

Question

Suppose you have a trigger on MY_CUSTOMER_TABLE and that it has a variable declared of type MY_CUSTOMER_TABLE%ROWTYPE. How can I assign the OLD value into that variable?

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;

Edit:

To clarify, this came about because I am using triggers to archive rows from MY_CUSTOMER_TABLE into MY_CUSTOMER_TABLE_HISTORY. Depending upon the action being performed (INSERT, UPDATE, DELETE), I need all the fields from either OLD or 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;

In this scenario, PL/SQL will not let me pass :OLD or :NEW to a procedure that expects a MY_CUSTOMER_TABLE%ROWTYPE argument.

Answer 1

You can't. It is generally bad practice to refer to all columns (like SELECT *) and you should specify the columns you need.

Answer 2

In the docs you will find that :old and :new are column values, not row types. So you will have to construct your rowtype manually.

trigger ....
  l_row  mytable%rowtype;
begin
  l_row.column1 := :old.column1;
  l_row.column2 := :old.column2;
  ...

  archive_function(l_row);
end;