I have an orders table and an order_products_items table.
The order_products_items has these fields:
- order_id
- product_id
- quantity
- price
I am trying to create a calculated_field: calculated_total_products_price in the orders table through a before insert trigger function that would calculate the total order price by looping through all the order_products_items related to the order and multiplying the quantity with the price for every order item.
This is my failed attempt in doing so:
CREATE OR REPLACE FUNCTION public.fn_trigger_total_order_price()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare total float := 0.0;
product record;
BEGIN
FOR product IN
SELECT itm.price, itm.quantity
FROM order_products_items itm
INNER JOIN orders ord
ON itm.order_id = ord.id
WHERE ord.id = NEW.id
LOOP
total = total + (product.price * product.quantity);
END LOOP;
NEW.calculated_total_products_price := total;
RETURN NEW;
END;
$function$
;
The trigger looks like this:
CREATE TRIGGER fn_trigger_total_order_price BEFORE INSERT ON public.orders
FOR EACH ROW EXECUTE PROCEDURE fn_trigger_total_order_price();
Somehow, I do not get any errors, but always get 0 as a result.
Am I missing something? or is there a better/more efficient way of approaching this?
Many thanks.
>Solution :
It looks like the issue with your current implementation is that you’re trying to access the newly inserted row in the orders table, which doesn’t exist yet in the BEFORE INSERT trigger. Instead, you should be joining the order_products_items table with the NEW variable, which represents the row that’s about to be inserted into the orders table.
It should look something like this:
FOR product IN
SELECT itm.price, itm.quantity
FROM order_products_items itm
WHERE itm.order_id = NEW.id
LOOP
total = total + (product.price * product.quantity);
END LOOP;
You also don’t need to join with the orders table because you already have the order_id in the order_products_items table.
Also, you can think of using a subquery to calculate the total price instead of a loop.
SELECT SUM(itm.price * itm.quantity) as total_price
FROM order_products_items itm
WHERE itm.order_id = NEW.id
You can assign this value to the NEW.calculated_total_products_price and return the new row.
Another thing you can consider is to use a BEFORE INSERT OR UPDATE trigger, that way when you update the quantity or price of an order item you can update the total_price field of the corresponding order.