How can I modify the trigger and make sure that the trigger runs properly to increase the OrderTotal value

For this situation I want to create the trigger and make sure that the trigger is executed after the insertion of the PurchaseOrderDetail record to increase the orderTotal in the PurchaseOrder table after each purchaseOrderDetail record is inserted.

This is my PurchaseOrder Table

create table PurchaseOrder (
OrderNo varchar2(5) not null,
SupplierID varchar2(5),
OrderDate number(8),
WarehouseID varchar2(5),
OrderTotal number(5),
constraint FK_Supplier_ID foreign key (SupplierID)
references Supplier (SupplierID) on delete cascade,
constraint FK_Warehouse_ID foreign key (WarehouseID)
references Warehouse (WarehouseID) on delete cascade,
constraint pk_PurchaseOrder primary key(OrderNo)
);

And this is my PurchaseOrderDetail Table

create table PurchaseOrderDetail (
OrderNo varchar2(5) not null,
OrderLineNo varchar2(5),
ItemNo varchar2(5),
OrderQty number(2),
OrderUnitCost number(4),
constraint pk_PurchaseOrderDetail primary key(OrderNo),
constraint FOR_Order_NO foreign key (OrderNo)
references PurchaseOrder (OrderNo) on delete cascade,
constraint FEG_Item_NO foreign key (ItemNo)
references Item (ItemNo) on delete cascade
);

This is the data for inserting

PurchaseOrder :
OrderNo SupplierID OrderDate WarehouseID OrderTotal
00001   00002      20191001  00003       75765
00002   00002      20191001  00001       84000
00003   00001      20191001  00002       14600

PurchaseOrderDetail :
OrderNo OrderLineNo ItemNo OrderQty OrderUnitCost
00001   00001       00001  85       645
00001   00002       00003  6        3490
00002   00001       00002  20       4200
00003   00001       00004  20       730

And this is the trigger that I created:

CREATE OR REPLACE TRIGGER increase_orderTotal
AFTER INSERT ON PurchaseOrderDetail 
FOR EACH ROW

BEGIN
    UPDATE PurchaseOrder
    SET OrderTotal = PurchaseOrder.OrderTotal + ( PurchaseOrderDetail.orderQty * PurchaseOrderDetail.OrderUnitCost ) 
    WHERE OrderNo = :PurchaseOrderDetail.OrderNo;
END;
/

But the trigger cannot be executed and shows “Warning:trigger created with compilation errors” error , how can I fix the trigger to run it properly?

Answer

You should use the :NEW as follows:

CREATE OR REPLACE TRIGGER INCREASE_ORDERTOTAL AFTER
    INSERT ON PURCHASEORDERDETAIL
    FOR EACH ROW
BEGIN
    UPDATE PURCHASEORDER
       SET
        ORDERTOTAL = ORDERTOTAL + ( :NEW.ORDERQTY * :NEW.ORDERUNITCOST )
     WHERE ORDERNO = :NEW.ORDERNO;
END;
/

Advertisement

Answer