Can I copy :OLD and :NEW pseudo-records in/to an Oracle stored procedure?

Question

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.

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.

Am I missing something or is there no way to avoid enumerating every :NEW and :OLD column as I invoke my insert procedure?

I want to do the following:

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;

But I'm stuck doing this:

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;

Okay, so it doesn't look like a big difference, but this is just an example with 3 columns rather than dozens.

Answer 1

I don't think it's possible like that. Documentation doesn't mention anything like that.

This would certainly cost performance, but you could try to define your trigger AFTER INSERT and another one BEFORE UPDATE OR DELETE, and in the trigger do something like:

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

and then call your procedure with that rowtype_variable.

Answer 2

It isn't. You have to do it yourself through enumeration.

The reasons it can't/doesn't work automatically include:

• 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.

• 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.