What is the correct way to solve ORA-38104 issue with trigger AFTER INSERT or UPDATE with merge?

Question

Oracle

Hello.
to solve the problem, it was necessary to create a trigger that would monitor the AFTER INSERT or UPDATE operations.

/*таблица за которой будет следить триггер
*/
create table ALL_EVENTS
(
ID varchar2(255),
LAST_USER varchar2(100),
VALUE number,
TIME_RECIEVED timestamp
)
;

/*таблица лог слежения*/
-- Create table
create table ALL_EVENTS_LOG
(
  id            VARCHAR2(255),
/*  type          VARCHAR2(50),*/
  time_received TIMESTAMP(6)
)
;

/*триггер, c merge не генерит дубль если в логе уже есть информация об ID */

CREATE OR REPLACE TRIGGER ALL_EVENTS_LOG
  AFTER INSERT or UPDATE ON ALL_EVENTS
FOR EACH ROW
declare
  -- local variables here
begin
merge into ALL_EVENTS_LOG log
using DUAL on (log.id=:OLD.id)
WHEN MATCHED THEN UPDATE SET log.id=:OLD.id
  WHEN NOT MATCHED THEN INSERT (ID, TIME_RECEIVED) VALUES (:NEW.id, sysdate);
end ALL_EVENTS_LOG;

when trying to execute insert

insert into ALL_EVENTS values (to_char(abs(dbms_random.random)), 'user1',abs(dbms_random.random),sysdate);

return ORA-38104 exception

ORA-38104: Columns referenced in the ON Clause cannot be updated: "LOG"."ID"
ORA-06512: at "ALL_EVENTS_LOG", line 4
ORA-04088: error during execution of trigger 'ALL_EVENTS_LOG'

I understand that I'm making some simple mistake somewhere ...
How to solve this problem correctly?

Answer 1

Solutions from another resource:
1
2