CODEWITHSUNDEEP
oracle-databasetriggers
Max
Max

Reputation: 1

Oracle Trigger on two tables

I am trying to create a BEFORE UPDATE trigger on the two tables purchaseorderdetail and purchaseorderheader as shown below. I would like to implement this trigger on PurchaseOrderHeader table that prohibits updates of the PurchaseOrderHeader.SubTotal column if the corresponding data in the PurchaseOrderDetail table is not consistent with the new value of the PurchaseOrderHeader.SubTotal column.

CREATE TABLE purchaseorderheader(
purchaseorderid NUMBER(4),
revisionnumber NUMBER(2),
status NUMBER(1),
employeeid NUMBER(3),
vendorid NUMBER(4),
shipmethodid NUMBER(1),
orderdate TIMESTAMP,
shipdate TIMESTAMP,
subtotal FLOAT(10),
taxamt FLOAT(10),
freight FLOAT(10),
modifieddate TIMESTAMP,
PRIMARY KEY(purchaseorderid)
);

CREATE TABLE purchaseorderdetail(
purchaseorderid NUMBER(4),
purchaseorderdetailid NUMBER(4),
duedate TIMESTAMP,
orderqty NUMBER(6),
productid NUMBER(6),
unitprice FLOAT(10),
receivedqty FLOAT(10),
rejectedqty FLOAT(10),
modifieddate TIMESTAMP,
PRIMARY KEY(purchaseorderdetailid),
CONSTRAINT fk_orderid FOREIGN KEY (purchaseorderid) REFERENCES purchaseorderheader(purchaseorderid)
);

What I have come up with so far, any help?

CREATE OR REPLACE TRIGGER Header_Before_Subtotal BEFORE UPDATE  
ON purchaseorderheader 
FOR EACH ROW
BEGIN
    IF(:NEW.subtotal <> (SELECT unitprice*orderqty FROM purchaseorderdetail GROUP BY purchaseorderid)) THEN
        RAISE_APPLICATION_ERROR(-20001, 'Subtotal is not equal to unitprice * order quantity, check again.');
    END IF;
END;

Upvotes: -1

Views: 36

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Compute total separately (and reference the master key), and then compare that value to new subtotal.

CREATE OR REPLACE TRIGGER Header_Before_Subtotal 
  BEFORE UPDATE  
  ON purchaseorderheader 
  FOR EACH ROW
DECLARE
  l_total number;
BEGIN
  SELECT d.unitprice * d.orderqty
    INTO l_total
    FROM purchaseorderdetail d
    WHERE d.purchaseorderid = :new.purchaseorderid;

  IF :NEW.subtotal <> l_total THEN
      RAISE_APPLICATION_ERROR(-20001, 'Subtotal is not equal to unitprice * order quantity, check again.');
  END IF;
END;
/

Upvotes: 2

Related Questions