PL/SQL Trigger - Dynamically reference :NEW or :OLD

Is it possible to dynamically reference the :NEW/OLD pseudo records, or copy them?

I'm doing a audit trigger for a very wide table, so would like to avoid having separate triggers for insert/delete/update.

When updating/inserting I want to record the :NEW values in the audit table, when deleting I want to record the :OLD values.

From stackoverflow

• Why don't you use Oracle's built in standard or fine-grained auditing?

  Matthew Watson : Standard Edition.

• You can use a compound trigger and programmatically check if it us I/U/D.

  Compound Triggers

  Regards K

  Matthew Watson : That is what I'm doing, but I'm trying to avoid having multiple insert statements within the trigger.

  Khb : I'm not sure I follow, are you doing something like this? CREATE OR REPLACE TRIGGER TRG BEFORE INSERT OR UPDATE OR DELETE ON TBL BEGIN IF INSERTING THEN ...(record :new) ELSEIF UPDATING THEN ...(record :new) ELSEIF DELETING THEN ...(record :old) END IF; ?

  Matthew Watson : I have a single insert statement INSERT INTO HIST ( EMP_ID, NAME ) VALUES (:NEW.EMP_ID , :NEW.NAME ) ; when deleting though, I want to use :OLD , not not have a seperate insert statement for that.

• You could try:

  declare
    l_deleting_ind varchar2(1) := case when DELETING then 'Y' end;
  begin
    insert into audit_table (col1, col2)
    values
     ( CASE WHEN l_deleting_ind = 'Y' THEN :OLD.col1 ELSE :NEW.col1 END
     , CASE WHEN l_deleting_ind = 'Y' THEN :OLD.col2 ELSE :NEW.col2 END
     );
  end;
  

  I found that the variable was required - you can't access DELETING directly in the insert statement.

• WOW, You want to have only ONE insert in your trigger to avoid what?

  *"I have a single insert statement INSERT INTO HIST ( EMP_ID, NAME ) VALUES (:NEW.EMP_ID , :NEW.NAME ) ; when deleting though, I want to use :OLD , not not have a seperate insert statement for that. "*

  It's a wide table. SO? It's not like there no REPLACE in text editors, you're not going to write the Insert again, just copy, paste, select, replace :NEW with :OLD.

  Tony does have a solution but I seriously doubt that performs better than 2 inserts would perform.

  What's the big deal?

  ---

  EDIT

  the main thing I'm trying to avoid is having to managed 2 inserts when the table changes. – Matthew Watson

  I battle this attitude all the time. Those who write Java or C++ or .Net have a built-in RBO... Do this, this is good. Don't do that, that's bad. They write code according to these rules and that's fine. The problem is when these rules are applied to databases. Databases don't behave the same way code does.

  In the code world, having essentially the same code in two "places" is bad. We avoid it. One would abstract that code to a function and call it from the two places and thus avoid maintaining it twice, and possibly missing one, etc. We all know the drill.

  In this case, while it's true that in the end I recommend two inserts, they are separated by an ELSE. You won't change one and forget the other one. IT'S Right There. It's not in a different package, or in some compiled code, or even somewhere else in the same trigger. They're right beside each other, there's an ELSE and the Insert is repeated with :NEW, instead of :OLD. Why am I so crazed about this? Does it really make a difference here? I know two inserts won't be worse than other ideas, and it could be better.

  The real reason is being prepared for the times when it does matter. If you're avoiding two inserts just for the sake of maintenance, you're going to miss the times when this makes a HUGE difference.

  INSERT INTO log
  SELECT * FROM myTable 
  WHERE flag = 'TRUE'
  
  ELSE                          -- column omitted for clarity
  
  INSERT INTO log
  SELECT * FROM myTable 
  WHERE flag = 'FALSE'
  

  Some, including Matthew, would say this is bad code, there are two inserts. I could easily replace 'TRUE' and 'FALSE' with a bind variable and flip it at will. And that's what most people would do. But if True is .1% of the values and 99.9% is False, you want two inserts, because you want two execution plans. One is better off with an index and the other an FTS. So, yes, you do have two Inserts to maintain. That's not always bad and in this case it's good and desirable.

  Matthew Watson : the main thing I'm trying to avoid is having to managed 2 inserts when the table changes.

• Use a compound trigger, as others have suggested. Save the old or new values, as appropriate, to variables, and use the variables in your insert statement:

  declare
    v_col1  table_name.col1%type;
    v_col2  table_name.col2%type;
  begin
    if deleting then
      v_col1 := :old.col1;
      v_col2 := :old.col2;
    else
      v_col1 := :new.col1;
      v_col2 := :new.col2;
    end if;
  
    insert into audit_table(col1, col2)
    values(v_col1, v_col2);
  end;
  

  Matthew Watson : mm, yeh, was hoping to be able to just copy the record. oh well. thanks