如何修改触发器并确保触发器正确运行以增加OrderTotal值

Question

在这种情况下，我想创建触发器，并确保在插入PurchaseOrderDetail记录后执行触发器，以在插入每个purchaseOrderDetails记录后增加PurchaseOrder表中的orderTotal。

这是我的采购订单表

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

这是我的采购订单详细信息表

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

这是插入的数据

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

这就是我创建的触发器：

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

但是触发器不能被执行；警告：使用编译错误创建触发器"；错误，如何修复触发器以正确运行它？

Answer 1

您应该按如下方式使用:NEW：

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