pl/sql statement Trigger

Question

Suppose we have the following two tables:

OrderHeader(OrderID, Odate, CustID, Total)

Order_Item(OrderID,ItemID, Qty, Subtotal)

Write a statement-level trigger that updates the Total in the orderHeader table with the total value of the order_item records whenever an insert, update or delete event occurs on the order_item table. For any update error, raise an exception.

So far I have written this:

create or replace TRIGGER SECURE_ORDER
AFTER INSERT OR DELETE OR UPDATE ON Order_Item
declare
 sum1 Number;
BEGIN
SELECT SUM(Qty*Price) Into sum1 FROM Order_Item Inner join Item Using(ItemID) WHERE OrderID=:OLD.OrderID;
UPDATE OrderHeader set Total=sum1 where OrderID=:OLD.OrderID;
EXCEPTION
    WHEN too_many_rows then
        dbms_output.put_line('Too many rows');
    WHEN no_data_found then
        dbms_output.put_line('No Data Found');
    WHEN others then
        dbms_output.put_line('other error');
END;

It is not correct however as the question statement says I have to write a statement level trigger which doesn't give me acces to NEW and OLD keyword. I don't know how to go about doing this in a statement level trigger. Any help would be appreciated.

Answer 1

You can just use a single MERGE statement:

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;
/

Then, if you have the tables:

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
);

Then if you:

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 );

The OrderHeader table contains:

SELECT * FROM OrderHeader;

ORDERID | TOTAL
------: | ----:
      1 |    10

Then if you do:

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

The OrderHeader table contains:

SELECT * FROM OrderHeader;

ORDERID | TOTAL
------: | ----:
      1 |    15

Then if you do:

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

The OrderHeader table contains:

SELECT * FROM OrderHeader;

ORDERID | TOTAL
------: | ----:
      1 |     8

And, finally, if you do:

DELETE FROM Order_Item;

The OrderHeader table contains:

SELECT * FROM OrderHeader;

ORDERID | TOTAL
------: | ----:
      1 |     0

db<>fiddle here