Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Item (
  ItemID NUMBER(10,0)
         GENERATED ALWAYS AS IDENTITY
         CONSTRAINT Item__ItemID__PK PRIMARY KEY,
  Name   VARCHAR2(20)
         NOT NULL,
  Price  NUMBER(12,2)
         NOT NULL
         CONSTRAINT Item__Price_GTE_0__CHK CHECK ( Price >= 0 )
);
CREATE TABLE Order_Item (
  OrderID INT
          -- CONSTRAINT Order_Item__OrderID__FK REFERENCES Orders ( OrderID )
          NOT NULL,
  Qty     INT
          NOT NULL
          CONSTRAINT Order_Item__Qty_GT_0__CHK CHECK ( Qty > 0 ),
  ItemID  INT
          NOT NULL
          CONSTRAINT Order_Item__ItemID__FK REFERENCES Item ( ItemId ),
  CONSTRAINT Order_Item__OID__IID__PK PRIMARY KEY ( OrderID, ItemID )
);
CREATE TABLE OrderHeader (
  OrderID INT
          NOT NULL,
  total   NUMBER(14,2)
          NOT NULL
);
CREATE TRIGGER SECURE_ORDER
AFTER INSERT OR DELETE OR UPDATE ON Order_Item
BEGIN
  MERGE INTO OrderHeader dst
  USING (
    SELECT COALESCE( oh.OrderID, o.OrderID ) AS OrderID,
           COALESCE( SUM( o.Qty*i.Price ), 0 ) AS total
    FROM   OrderHeader oh
           FULL OUTER JOIN Order_Item o
           ON oh.OrderID = o.OrderID
           LEFT OUTER JOIN Item i
           ON ( o.ItemID = i.ItemID )
    GROUP BY COALESCE( oh.OrderID, o.OrderID )
  ) src
  ON ( src.OrderID = dst.OrderID )
  WHEN MATCHED THEN
    UPDATE SET total = src.total
  WHEN NOT MATCHED THEN
    INSERT ( OrderID, Total ) VALUES ( src.OrderID, src.Total );
END;
/
BEGIN
  INSERT INTO Item ( Name, Price ) VALUES ( 'Item 001', 1.00 );
  INSERT INTO Item ( Name, Price ) VALUES ( 'Item 002', 2.00 );
  INSERT INTO Item ( Name, Price ) VALUES ( 'Item 003', 2.50 );
  INSERT INTO Order_Item ( OrderID, ItemID, Qty ) VALUES ( 1, 1, 3 );
  INSERT INTO Order_Item ( OrderID, ItemID, Qty ) VALUES ( 1, 2, 1 );
  INSERT INTO Order_Item ( OrderID, ItemID, Qty ) VALUES ( 1, 3, 2 );
END;
/
1 rows affected

SELECT * FROM OrderHeader;


ORDERID
TOTAL


1
10


UPDATE Order_Item
SET   Qty     = 8
WHERE OrderID = 1
AND   ItemID  = 1;
1 rows affected

SELECT * FROM OrderHeader;


ORDERID
TOTAL


1
15


DELETE FROM Order_Item
WHERE  ( OrderID, ItemID ) IN ( (1,2), (1,3) );
2 rows affected

SELECT * FROM OrderHeader;


ORDERID
TOTAL


1
8


DELETE FROM Order_Item;
1 rows affected

SELECT * FROM OrderHeader;


ORDERID
TOTAL


1
0