Prepare lock test tables

CREATE TABLE A ( 
  ID           NUMBER(10)    NOT NULL, 
  DESCRIPTION  VARCHAR2(50)  NOT NULL, 
  CREATIONUSER VARCHAR2(50)  NOT NULL, 
  UPDATEUSER   VARCHAR2(50)  NOT NULL 
  )

ALTER TABLE A ADD ( 
  CONSTRAINT A_PK PRIMARY KEY (ID))

CREATE SEQUENCE SEQ_A START WITH 1

CREATE OR REPLACE TRIGGER TR_BR_A  
BEFORE INSERT OR UPDATE OR DELETE ON A  
FOR EACH ROW 
DECLARE 
    tmpVar NUMBER; 
 
BEGIN 
    IF INSERTING THEN 
       select SEQ_A.nextval into tmpVar from dual; 
       :new.ID := tmpVar; 
       :new.CREATIONUSER := USER; 
    END IF; 
 
    IF NOT DELETING THEN 
        :new.UPDATEUSER := USER; 
    END IF; 
END; 

CREATE TABLE B ( 
  ID           NUMBER(10)    NOT NULL, 
  B_FK_A       NUMBER(10)    NOT NULL, 
  CREATIONUSER VARCHAR2(50)  NOT NULL, 
  UPDATEUSER   VARCHAR2(50)  NOT NULL 
  )

ALTER TABLE B ADD ( 
  CONSTRAINT B_PK PRIMARY KEY (ID))

CREATE SEQUENCE SEQ_B START WITH 1

CREATE OR REPLACE TRIGGER TR_BR_B  
BEFORE INSERT OR UPDATE OR DELETE ON B  
FOR EACH ROW 
DECLARE 
    tmpVar NUMBER; 
 
BEGIN 
    IF INSERTING THEN 
       select SEQ_B.nextval into tmpVar from dual; 
       :new.ID := tmpVar; 
       :new.CREATIONUSER := USER; 
    END IF; 
 
    IF NOT DELETING THEN 
        :new.UPDATEUSER := USER; 
    END IF; 
END; 

ALTER TABLE B ADD ( 
  CONSTRAINT C_B_FK_A 
  FOREIGN KEY (B_FK_A)  
  REFERENCES A (ID) 
  ENABLE VALIDATE)

CREATE INDEX IDX_B_FK_A ON B (B_FK_A) LOGGING NOPARALLEL

INSERT into A (DESCRIPTION) values ('test')

INSERT into A (DESCRIPTION) values ('test2')

INSERT into B (B_FK_A) VALUES (1)

INSERT into B (B_FK_A) VALUES (2)

commit

This UPDATE locks both tables without updating the ID column

UPDATE A set description = 'Live' where id = 1

commit

Splitting the one BR trigger into special trigger for INSERT and UPDATE

DROP TRIGGER TR_BR_A

CREATE OR REPLACE TRIGGER TR_BRI_A   
BEFORE INSERT ON A   
FOR EACH ROW  
DECLARE  
    tmpVar NUMBER;  
  
BEGIN  
   select SEQ_A.nextval into tmpVar from dual;  
   :new.ID := tmpVar;  
   :new.CREATIONUSER := USER;  
   :new.UPDATEUSER := USER;  
END; 

CREATE OR REPLACE TRIGGER TR_BRU_A  
BEFORE UPDATE ON A  
FOR EACH ROW 
 
BEGIN 
    :new.UPDATEUSER := USER; 
END; 

Now the UPDATE will only lock table A

UPDATE A set description = 'Live2' where id = 2

commit