Trigger to calculate subtotal

Question

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.

Answer 1

The mutation error does not occur because you are updating the table; it occurs because you are querying from the table that is already being updated.

If I'm understanding correctly what you want to do:

CREATE OR REPLACE TRIGGER create_subtotal  
BEFORE INSERT OR UPDATE ON Order_Line 
for each row
DECLARE 
  currentPrice  products.price%TYPE;
BEGIN
  -- Get the current price for the product
  SELECT price INTO currentPrice FROM products WHERE product_no = :new.product_no;

  -- Set the new subtotal to the current price multiplied by the order quantity
  :new.subtotal := currentPrice * :new.quantity;
END;
/

(I'm unclear why you have a test for a quantity below 0, and what you want to occur in this case. If you want to set the subtotal to NULL or 0 in this case, it should be quite easy to modify the above.)

Answer 2

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