How to avoid ORA-04091 mutating table error

Hi,

I have a simple table: (oracle 12.2 SE linux 8)

SUPERUSER@192.168.16.6/ABIECTEST 16-JUL-24> desc abiec_ownership;
Name Null? Type

ID NOT NULL NUMBER(19)
ANIMALID NUMBER(19)
CONFIDENTIAL NUMBER(1)
CREATEDAT NOT NULL TIMESTAMP(6)
OWNERID NOT NULL NUMBER(19)
SEQUENCENUMBER NOT NULL NUMBER(5)
STARTDATE VARCHAR2(255 CHAR)
VERSION NOT NULL NUMBER(10)
ENDDATE_ABIEC_OWNERSHIP VARCHAR2(255 CHAR)

the meaning is that (animalId, sequenceNumber) is unique.

An app inserts an empty row with a sequence number.

then updates the animalId

and in this case I want to update ENDDATE_ABIEC_OWNERSHIP for the previous SEQUENCENUMBER for the same animalId.

So I created a trigger:

CREATE OR REPLACE TRIGGER trg_abiec_ownership_bef_ins