totalPayment xxxxxxxxxxxxxxxxx(How to create trigger after update to get the value totalPayment)

totalPayment xxxxxxxxxxxxxxxxx(How to create trigger after update to get the value totalPayment)

我想创建一个触发器来计算Event表上的totalPayment。 但是要计算totalPayment列，它会引用另一个表，即位置和设备。 下面是表的表结构：

I want to create a trigger to calculate the totalPayment on Event table. But to calculate the totalPayment column, it a references with another two table which is Location and Equipment. Below is the table structure for the tables:

create table equipment( equipmentID varchar2(10) primary key, equipmentName varchar2(50) not null, equipmentPriceUnit decimal(18,2) not null ); create table location( locationID varchar2(10) primary key, locationName varchar2(50) not null, locationMaxCapacity int not null, locationPriceHour decimal(18,2) not null ); create table event( eventID varchar2(10) primary key, eventName varchar2(50) not null, eventDurationHour int not null, noAudience int not null, equipmentID references equipment(equipmentID) null, equipmentQuantity int null, locationID references location(locationID) null, **totalPayment decimal(18,2) null** );

the equation to do this calculation is

totallocation := locationPriceHour*eventDurationHour; totalequipment := equipmentPriceUnit*equipmentQuantity; totalPayment :=totallocation + totalequipment;

最满意答案

尝试这个：

CREATE OR REPLACE TRIGGER trg_event_update AFTER UPDATE ON event FOR EACH ROW REFERENCING new AS new old AS old DECLARE totalLocation location.locationPriceHour%TYPE; totalequipment equipment.equipmentPriceUnit%TYPE; ... -- declaration variables BEGIN SELECT l.locationPriceHour * :new.eventDurationHour INTO totalLocation FROM location WHERE locationID = :new.locationID; SELECT e.equipmentPriceUnit * :new.equipmentQuantity INTO totalequipment FROM equipment WHERE equipmentID = :new.equipmentID; :new.totalPayment := totalLocation + totalequipment; END; /

我建议不要使用计算字段。 在运行时计算值，如下所示：

SELECT (SELECT l.locationPriceHour FROM location l WHERE l.locationID = ev.locationID) * ev.eventDurationHour + (SELECT eq.equipmentPriceUnit FROM equipment eq WHERE eq.equipmentID = ev.equipmentID) * ev.equipmentQuantity AS totalPayment FROM event ev;

Try this:

CREATE OR REPLACE TRIGGER trg_event_update AFTER UPDATE ON event FOR EACH ROW REFERENCING new AS new old AS old DECLARE totalLocation location.locationPriceHour%TYPE; totalequipment equipment.equipmentPriceUnit%TYPE; ... -- declaration variables BEGIN SELECT l.locationPriceHour * :new.eventDurationHour INTO totalLocation FROM location WHERE locationID = :new.locationID; SELECT e.equipmentPriceUnit * :new.equipmentQuantity INTO totalequipment FROM equipment WHERE equipmentID = :new.equipmentID; :new.totalPayment := totalLocation + totalequipment; END; /

I would suggest not to use the calculated field. Calculate the values at runtime instead, like this:

SELECT (SELECT l.locationPriceHour FROM location l WHERE l.locationID = ev.locationID) * ev.eventDurationHour + (SELECT eq.equipmentPriceUnit FROM equipment eq WHERE eq.equipmentID = ev.equipmentID) * ev.equipmentQuantity AS totalPayment FROM event ev;