Is it ever possible to design an Oracle trigger that modifies same table (guaranteed not same row)?

Question

I need to write a TRIGGER that preserves the old value of a column before it is updated, by inserting or updating the old value into another row in the same table. (yes, I know). The following MERGE/DUAL trickery has served me well, but because in this case, I'm inserting into or updating the same table, Oracle complains at runtime. Also, for some reason, I found it unusually difficult to write code that compiles without errors.

Two questions:

1. Is it ever possible to modify the same table that the trigger is on, even when I can guarantee that the trigger will never update the row that triggered the trigger? Or do I have to do something like (e.g.): insert pending changes into another table, so that a 2nd trigger can merge them back into the original table? (This table is a customer interface, so I can't re-architect this to use a second table for permanently storing old values.)
2. What's with the compiler errors that don't let me use :old.event_key, but do let me use :old.property_val in the MERGE statement? (declaring a variable old_event_key and assigning it to the value of :old.event_key seems to work) Is there some sort of hidden intermediate language that knows when a column is (part of) the primary key, and prevents you from referencing it via :old.?

Here is the offending code:

create or replace trigger remember_old_status
  before update on event_properties
  for each row
    when (old.property_name = 'CURRENT-STATUS')
    declare
      old_event_key varchar2(20);
    begin
      old_event_key := :old.event_key;
      merge into event_properties eprop
        using (select 1 from dual) dummy
        on (    eprop.event_key = old_event_key
            AND eprop.property_name = 'PREVIOUS-STATUS')
        when matched then
          update set property_val = :old.property_val
        when not matched then
          insert (event_key, property_name, property_val)
          values (old_event_key, 'PREVIOUS-STATUS', :old.property_val);
    end;

And here's the table:

CREATE TABLE "CUST"."EVENT_PROPERTIES" 
 (  "EVENT_KEY" VARCHAR2(20 BYTE) CONSTRAINT "NN_FLE_FLK" NOT NULL ENABLE, 
  "PROPERTY_NAME" VARCHAR2(20 BYTE) CONSTRAINT "NN_FLE_PN" NOT NULL ENABLE, 
  "PROPERTY_VAL" VARCHAR2(80 BYTE), 
   CONSTRAINT "PX_EVENT_PROPERTIES" PRIMARY KEY ("EVENT_KEY", "PROPERTY_NAME") DEFERRABLE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CUST_TS"  ENABLE
 ) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CUST_TS" ;

And here are the error messages:

ORA-04091: table CUST.EVENT_PROPERTIES is mutating, trigger/function may not see it ORA-06512: at "CUST.REMEMBER_OLD_STATUS", line 5

Answer 1

You could use a compound trigger to do this, storing the old values in variables in a before each row section, and then merging in an after statement.

This assumes you'll only ever update one row at a time:

create or replace trigger remember_old_status
for update on event_properties
compound trigger

  old_rec event_properties%rowtype;

  before each row is
  begin
    if (:old.property_name = 'CURRENT-STATUS') then
      old_rec.event_key := :old.event_key;
      old_rec.property_name := :old.property_name;
      old_rec.property_val := :old.property_val;
    end if;
  end before each row;

  after statement is
  begin
    if (old_rec.property_name = 'CURRENT-STATUS') then
      merge into event_properties eprop
        using (
          select old_rec.event_key as event_key, 
            'PREVIOUS-STATUS' as property_name,
            old_rec.property_val as property_val
            from dual
        ) dummy
        on (eprop.event_key = dummy.event_key
            and eprop.property_name = dummy.property_name)
        when matched then
          update set property_val = old_rec.property_val
        when not matched then
          insert (event_key, property_name, property_val)
          values (dummy.event_key, dummy.property_name, dummy.property_val);
    end if;
  end after statement;
end remember_old_status;
/

Quick test:

insert into event_properties values('SOME_EVENT', 'CURRENT-STATUS', 'A');

1 row inserted.

update event_properties set property_val = 'B' where event_key = 'SOME_EVENT' and property_name = 'CURRENT-STATUS';

1 row updated.

select * from event_properties;

EVENT_KEY            PROPERTY_NAME        PROPERTY_VAL                                                                    
-------------------- -------------------- --------------------------------------------------------------------------------
SOME_EVENT           CURRENT-STATUS       B                                                                               
SOME_EVENT           PREVIOUS-STATUS      A                                                                               

update event_properties set property_val = 'C' where event_key = 'SOME_EVENT' and property_name = 'CURRENT-STATUS';

1 row updated.

select * from event_properties;

EVENT_KEY            PROPERTY_NAME        PROPERTY_VAL                                                                    
-------------------- -------------------- --------------------------------------------------------------------------------
SOME_EVENT           CURRENT-STATUS       C                                                                               
SOME_EVENT           PREVIOUS-STATUS      B                                                                               

If you want to deal with multiple updates on one statement then the before each row can populate a collection instead, and you can then use that in the after statement.

create or replace trigger remember_old_status
for update on event_properties
compound trigger

  type t_type is table of event_properties%rowtype;
  old_recs t_type := t_type();

  before each row is
  begin
    if (:old.property_name = 'CURRENT-STATUS') then
      old_recs.extend();
      old_recs(old_recs.count).event_key := :old.event_key;
      old_recs(old_recs.count).property_name := :old.property_name;
      old_recs(old_recs.count).property_val := :old.property_val;
    end if;
  end before each row;

  after statement is
  begin
    forall i in old_recs.first..old_recs.last
      merge into event_properties eprop
        using (
          select old_recs(i).event_key as event_key, 
            'PREVIOUS-STATUS' as property_name,
            old_recs(i).property_val as property_val
            from dual
        ) dummy
        on (eprop.event_key = dummy.event_key
            and eprop.property_name = dummy.property_name)
        when matched then
          update set property_val = old_recs(i).property_val
        when not matched then
          insert (event_key, property_name, property_val)
          values (dummy.event_key, dummy.property_name, dummy.property_val);
  end after statement;
end remember_old_status;
/