Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (id NUMBER, name VARCHAR2(20));
CREATE TABLE table2 (id NUMBER, name VARCHAR2(20));
CREATE OR REPLACE TRIGGER "TRG1"
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW
 BEGIN
   if nvl(:old.name, '-1') <> nvl(:new.name, '-1') then
     :new.name =(SELECT name FROM table2
        WHERE :new.id = table2.id);
   end if;
 END;
/
ORA-24344: success with compilation error

SELECT * FROM USER_ERRORS;


NAME
TYPE
SEQUENCE
LINE
POSITION
TEXT
ATTRIBUTE
MESSAGE_NUMBER


TRG1
TRIGGER
1
3
16
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   := . ( @ % ; indicator

ERROR
103


CREATE OR REPLACE TRIGGER "TRG1"
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW
 BEGIN
   if nvl(:old.name, '-1') <> nvl(:new.name, '-1') then
     :new.name :=(SELECT name FROM table2
        WHERE :new.id = table2.id);
   end if;
 END;
/
ORA-24344: success with compilation error

SELECT * FROM USER_ERRORS;


NAME
TYPE
SEQUENCE
LINE
POSITION
TEXT
ATTRIBUTE
MESSAGE_NUMBER


TRG1
TRIGGER
1
3
19
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   continue avg count current exists max min prior sql stddev
   sum variance execute forall merge time timestamp interval
   date <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string literal with character set specification>
   <an alternat
ERROR
103


INSERT INTO table2 (id, name) VALUES (1, 'Alice');
1 rows affected

INSERT INTO table1 (id, name) VALUES (1, 'Beryl');
ORA-04098: trigger 'FIDDLE_WMEHJDSKCAIVOGDYZKBB.TRG1' is invalid and failed re-validation

SELECT * FROM table1;