SQL> create table test (deptno number, dname varchar2(20));

Table created.

SQL> create or replace trigger trg_bi
  2    before insert on test
  3    for each row
  4  begin
  5    select dname into :new.dname
  6      from dept d
  7      where d.deptno = :new.deptno;
  8  end;
  9  /

Trigger created.

SQL> insert into test (deptno) values (20);

1 row created.

SQL> select * from test;

    DEPTNO DNAME
---------- --------------------
        20 RESEARCH

SQL>

create table ttab1
(
 tno number(4),
 tname varchar2(30),
 tcomm varchar2(30)
 );
 
create table ttab2
(
 tno1 number(4),
 tname1 varchar2(30),
 tcomm1 varchar2(30)
 );
 
CREATE OR REPLACE TRIGGER apps.ttab1_trig
   AFTER INSERT ON ttab1 FOR EACH ROW
   WHEN (new.tno = 10)
BEGIN
      UPDATE ttab1
           SET tcomm = (SELECT tcomm1
                          FROM ttab2
                         WHERE tname1 = :new.tname);
END ttab1_trig;

 insert into ttab2
 values(1, 'TEST', 'SHIP_M');

 commit;

 insert into ttab1
 values(10, 'TEST', '');