Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Orders (order_id char(10), part char(20))
create table Parts_inv(part_num char(50))
create table Parts_noninv(part_num char(50))
CREATE OR REPLACE TRIGGER ORDERS_TRG 
BEFORE INSERT OR UPDATE ON Orders
FOR EACH ROW
DECLARE
LV_COUNT NUMBER;
BEGIN
SELECT 1 INTO LV_COUNT FROM DUAL 
WHERE :NEW.part IN (
SELECT part_num FROM Parts_inv
UNION ALL
SELECT part_num FROM Parts_noninv
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE;
END ORDERS_TRG;
/
INSERT INTO Parts_inv VALUES ('PART1');
1 rows affected

INSERT INTO Parts_noninv VALUES ('PART2');
1 rows affected

SELECT * FROM Parts_inv;


PART_NUM


PART1


SELECT * FROM Parts_noninv;


PART_NUM


PART2


INSERT INTO Orders VALUES ('ORDER1','PART1');
1 rows affected

INSERT INTO Orders VALUES ('ORDER2','PART2');
1 rows affected

INSERT INTO Orders VALUES ('ORDER3','PART3');
ORA-01403: no data found
ORA-06512: at "FIDDLE_ZAFORWPCGAVAMEAIEMEO.ORDERS_TRG", line 12
ORA-06512: at "FIDDLE_ZAFORWPCGAVAMEAIEMEO.ORDERS_TRG", line 4
ORA-04088: error during execution of trigger 'FIDDLE_ZAFORWPCGAVAMEAIEMEO.ORDERS_TRG'

UPDATE Orders SET PART = 'PART4' WHERE ORDER_ID = 'ORDER1';
ORA-01403: no data found
ORA-06512: at "FIDDLE_ZAFORWPCGAVAMEAIEMEO.ORDERS_TRG", line 12
ORA-06512: at "FIDDLE_ZAFORWPCGAVAMEAIEMEO.ORDERS_TRG", line 4
ORA-04088: error during execution of trigger 'FIDDLE_ZAFORWPCGAVAMEAIEMEO.ORDERS_TRG'