触发计算小计 [英] Trigger to calculate subtotal

问题描述

一段时间以来，我一直在尝试实现此触发器，并且正在取得进展(我认为！)，但是现在我遇到了一个变异错误.

我在这里拥有的是三个实体(与这里相关)，Customer_Order(总计)，Order_Line(数量，小计等)和Products(库存，价格). Order_line是一个链接实体，因此一个产品可以包含在多个order_lines中，一个customer_order可以具有多个order_lines，但是order_line在一个订单中只能出现一次，并且只能包含一个产品.触发的目的是从order_line(或我实际认为的产品价格)中提取小计，从order_line中获取数量，将它们相乘并更新新的order_line的小计.

因此，我插入了一个带有产品外键的order_line，数量为3，价格为4.00，触发器将两者相乘等于12，并更新了小计.现在，我认为在这里使用price而不是Order_line的小计来纠正突变错误是正确的，这是因为我要让触发器更新由触发语句访问的表，对吗?如何解决数量问题?数量不一定总是与库存相同，它必须小于或等于库存，所以有人知道我该如何解决这个问题，以便从产品中选择并更新order_line?谢谢.

CREATE OR REPLACE TRIGGER create_subtotal  
BEFORE INSERT OR UPDATE ON Order_Line 
for each row
DECLARE 
currentSubTotal order_line.subtotal%type;
currentQuantity order_line.quantity%type;
BEGIN 
select order_line.subtotal,order_line.quantity
into currentSubTotal,currentQuantity
from order_line
where product_no = :new.product_no;
IF (currentquantity>-1 ) then 

update order_line set subtotal= currentSubTotal * currentQuantity where     line_no=:new.line_no;

END IF;
END; 
. 
run

我想我可以使用:new语法来使用触发语句中的数量值.我会尝试的，但是感谢您的确认和帮助，谢谢.

解决方案

听起来您想要类似的东西

CREATE OR REPLACE TRIGGER create_subtotal
  BEFORE INSERT OR UPDATE ON order_line
  FOR EACH ROW
DECLARE
  l_price products.price%type;
BEGIN
  SELECT price
    INTO l_price
    FROM products
   WHERE product_no = :new.product_no;

  IF( :new.quantity > -1 )
  THEN
    :new.subtotal := :new.quantity * l_price;
  END IF;
END;

但是，如果这不是家庭作业，那么在此触发器中从PRODUCTS表中提取价格确实没有任何意义.据推测，产品的价格会随着时间而变化.但是在下订单时，价格对于特定订单是固定的.如果仅在INSERT上定义触发器，则仅获取当前价格可能是合理的.但是，如果您想在行更新时重新计算行的小计，则需要从下订单时获取价格(并且假设您不向同一位客户同时收取不同的价格)时间).

从规范化的角度来看，将计算的字段存储在第一位也往往没有任何意义.最好将数量和价格存储在order_line表中，然后在视图中计算该行的小计(或者，如果使用11g，则作为表中的虚拟列).

I've been trying to implement this trigger for a while now and am making progress (I think!) but now I am getting a mutation error.

What I have here is three entities (that are relevant here), Customer_Order(total etc), Order_Line(quantity, subtotal etc) and Products(stock, price). Order_line is a link entity and so a product can be in many order_lines and a customer_order can have many order_lines, but an order_line can only appear once in an order and can only contain one product. The purpose of the trigger is to take the subtotal from order_line(or price from products I think actually) and the quantity from order_line, multiply them and update the new order_line's subtotal.

So I insert an order_line with my product foreign key, quantity of 3 and price of 4.00, the trigger multiplies the two to equal 12 and updates the subtotal. Now, I am thinking it's right to use price here instead of Order_line's subtotal in order to fix the mutation error (which occurs because I am asking the trigger to update the table which is being accessed by the triggering statement, right?), but how do I fix the quantity issue? Quantity won't always be the same value as stock, it has to be less than or equal to stock, so does anyone know how I can fix this to select from product and update order_line? Thanks.

CREATE OR REPLACE TRIGGER create_subtotal  
BEFORE INSERT OR UPDATE ON Order_Line 
for each row
DECLARE 
currentSubTotal order_line.subtotal%type;
currentQuantity order_line.quantity%type;
BEGIN 
select order_line.subtotal,order_line.quantity
into currentSubTotal,currentQuantity
from order_line
where product_no = :new.product_no;
IF (currentquantity>-1 ) then 

update order_line set subtotal= currentSubTotal * currentQuantity where     line_no=:new.line_no;

END IF;
END; 
. 
run

EDIT: I think I could use the :new syntax to use the quantity value from the triggering statement. I'll try this but I'd appreciate confirmation and help still, thanks.

解决方案

It sounds like you want something like

CREATE OR REPLACE TRIGGER create_subtotal
  BEFORE INSERT OR UPDATE ON order_line
  FOR EACH ROW
DECLARE
  l_price products.price%type;
BEGIN
  SELECT price
    INTO l_price
    FROM products
   WHERE product_no = :new.product_no;

  IF( :new.quantity > -1 )
  THEN
    :new.subtotal := :new.quantity * l_price;
  END IF;
END;

If this is something other than homework, however, it doesn't really make sense to pull the price from the PRODUCTS table in this trigger. Presumably, a product's price will change over time. But the price is fixed for a particular order when the order is placed. If the trigger was only defined on INSERT, it would probably be reasonable to just fetch the current price. But if you want to recalculate the subtotal of the line when a row is updated, you'd need to fetch the price as of the time the order was placed (and that assumes that you don't charge different customers different prices at the same time).

From a normalization standpoint, it also tends not to make sense to store calculated fields in the first place. It would make more sense to store the quantity and the price in the order_line table and then calculate the subtotal for the line in a view (or, if you're using 11g, as a virtual column in the table).

这篇关于触发计算小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！