Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (my_id NUMBER);
CREATE TABLE table2 (my_id NUMBER, new_id NUMBER);
create or replace trigger my_trigger
  Before insert or update on table1
  referencing new as new old as old 
  for each row 
declare
  id number;
  id_cnt PLS_INTEGER;
  v_new_id table2.new_id%TYPE;
begin 
  IF :new.my_id is null THEN
    RAISE_APPLICATION_ERROR(-20001,'MY_ID should nit be null');
  END IF;

  select count(*)
  INTO   id_cnt
  from   table2
  where  my_id=:new.my_id;

  if id_cnt=0 then
    RAISE_APPLICATION_ERROR(-20002,'not a valid id');
  else
    select new_id
    INTO   v_new_id
    from   table2
    where  my_id=:new.my_id;

    if length(v_new_id) < 5 then
      RAISE_APPLICATION_ERROR(-20003,'length is very small');
    END IF;
  END IF;
END my_trigger;
/
SELECT * FROM user_errors;
INSERT INTO table1 (my_id) VALUES (NULL);
ORA-20001: MY_ID should nit be null
ORA-06512: at "FIDDLE_UFOLMHZXZRFOAKZLUGJF.MY_TRIGGER", line 7
ORA-04088: error during execution of trigger 'FIDDLE_UFOLMHZXZRFOAKZLUGJF.MY_TRIGGER'

INSERT INTO table1 (my_id) VALUES (1);
ORA-20002: not a valid id
ORA-06512: at "FIDDLE_UFOLMHZXZRFOAKZLUGJF.MY_TRIGGER", line 16
ORA-04088: error during execution of trigger 'FIDDLE_UFOLMHZXZRFOAKZLUGJF.MY_TRIGGER'

INSERT INTO table2 (my_id, new_id) VALUES (1, 1);
1 rows affected

INSERT INTO table1 (my_id) VALUES (1);
ORA-20003: length is very small
ORA-06512: at "FIDDLE_UFOLMHZXZRFOAKZLUGJF.MY_TRIGGER", line 24
ORA-04088: error during execution of trigger 'FIDDLE_UFOLMHZXZRFOAKZLUGJF.MY_TRIGGER'

UPDATE table2 SET new_id = 123456;
1 rows affected

INSERT INTO table1 (my_id) VALUES (1);
1 rows affected