Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE PARTS_INV
  (PARTS_NUM  CHAR(50));
MERGE INTO PARTS_INV p
  USING (SELECT 'ABC123' AS PARTS_NUM FROM DUAL UNION ALL
         SELECT 'BCD234' FROM DUAL UNION ALL
         SELECT 'CDE345' FROM DUAL) d
    ON (d.PARTS_NUM = p.PARTS_NUM)
  WHEN NOT MATCHED THEN
    INSERT (PARTS_NUM) VALUES (d.PARTS_NUM);
3 rows affected

CREATE TABLE PARTS_NONINV
  (PARTS_NUM  CHAR(50));
MERGE INTO PARTS_NONINV p
  USING (SELECT 'DEF456' AS PARTS_NUM FROM DUAL UNION ALL
         SELECT 'EFG567' FROM DUAL UNION ALL
         SELECT 'FGH678' FROM DUAL) d
    ON (d.PARTS_NUM = p.PARTS_NUM)
  WHEN NOT MATCHED THEN
    INSERT (PARTS_NUM) VALUES (d.PARTS_NUM);
3 rows affected

CREATE TABLE ALL_PARTS
  (PARTS_NUM   CHAR(50),
   SOURCE      VARCHAR2(1) NOT NULL,
   CONSTRAINT PK_ALL_PARTS
     PRIMARY KEY (PARTS_NUM, SOURCE));
INSERT INTO ALL_PARTS
  SELECT PARTS_NUM, 'I' AS SOURCE FROM PARTS_INV
  UNION ALL
  SELECT PARTS_NUM, 'N' AS SOURCE FROM PARTS_NONINV;
6 rows affected

CREATE TRIGGER PARTS_INV_AID
  AFTER INSERT OR DELETE ON PARTS_INV
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO ALL_PARTS (PARTS_NUM, SOURCE) VALUES (:NEW.PARTS_NUM, 'I');
  ELSIF DELETING THEN
    DELETE FROM ALL_PARTS WHERE PARTS_NUM = :OLD.PARTS_NUM;
  END IF;
END PARTS_INV_AID;
/
CREATE TRIGGER PARTS_NONINV_AID
  AFTER INSERT OR DELETE ON PARTS_NONINV
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO ALL_PARTS (PARTS_NUM, SOURCE) VALUES (:NEW.PARTS_NUM, 'N');
  ELSIF DELETING THEN
    DELETE FROM ALL_PARTS WHERE PARTS_NUM = :OLD.PARTS_NUM;
  END IF;
END PARTS_NONINV_AID;
/
SELECT * FROM PARTS_INV;

PARTS_NUM

ABC123

CDE345

BCD234

SELECT * FROM PARTS_NONINV;

PARTS_NUM

DEF456

FGH678

EFG567

SELECT * FROM ALL_PARTS;

PARTS_NUM
SOURCE

ABC123
I

BCD234
I

CDE345
I

DEF456
N

EFG567
N

FGH678
N

INSERT INTO PARTS_INV (PARTS_NUM) VALUES ('GHI789');
1 rows affected

SELECT * FROM PARTS_INV;

PARTS_NUM

ABC123

CDE345

BCD234

GHI789

SELECT * FROM ALL_PARTS;

PARTS_NUM
SOURCE

ABC123
I

BCD234
I

CDE345
I

DEF456
N

EFG567
N

FGH678
N

GHI789
I

SELECT * FROM PARTS_NONINV;

PARTS_NUM

DEF456

FGH678

EFG567

INSERT INTO PARTS_NONINV (PARTS_NUM) VALUES ('HIJ890');
1 rows affected

SELECT * FROM PARTS_NONINV;

PARTS_NUM

DEF456

FGH678

EFG567

HIJ890

SELECT * FROM ALL_PARTS;

PARTS_NUM
SOURCE

ABC123
I

BCD234
I

CDE345
I

DEF456
N

EFG567
N

FGH678
N

GHI789
I

HIJ890
N

SELECT * FROM PARTS_INV;

PARTS_NUM

ABC123

CDE345

BCD234

GHI789

DELETE FROM PARTS_INV WHERE PARTS_NUM = 'BCD234';
1 rows affected

SELECT * FROM PARTS_INV;

PARTS_NUM

ABC123

CDE345

GHI789

SELECT * FROM ALL_PARTS;

PARTS_NUM
SOURCE

ABC123
I

CDE345
I

DEF456
N

EFG567
N

FGH678
N

GHI789
I

HIJ890
N

SELECT * FROM PARTS_NONINV;

PARTS_NUM

DEF456

FGH678

EFG567

HIJ890

DELETE FROM PARTS_NONINV WHERE PARTS_NUM = 'EFG567';
1 rows affected

SELECT * FROM PARTS_NONINV;

PARTS_NUM

DEF456

FGH678

HIJ890

SELECT * FROM ALL_PARTS;

PARTS_NUM
SOURCE

ABC123
I

CDE345
I

DEF456
N

FGH678
N

GHI789
I

HIJ890
N