Trigger/Function Update 1st table when update 2nd table?

[MrCBofBCinTX]

I have an existing database of products with prices and vendors,etc.
A script updates prices 1-4 times a month.

I now want to add a set of tables to describe an assembly of parts to make specific project items like a floor or wall of a particular size

Each project, like a 12' x 20' Floor will have an item from products table with a quantity. I made a subtotal column, which uses a function and trigger to calculate:

CREATE FUNCTION sum_assembly_part_subtotal() RETURNS "trigger" AS '
BEGIN                            
IF NEW.assembly_part_subtotal IS NULL THEN
NEW.assembly_part_subtotal := NEW.assembly_part_quantity*(SELECT price FROM products WHERE products.product_id=NEW.assembly_part_product_id);
END IF;
RETURN NEW;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER sum_assembly_part_subtotal_trigger
BEFORE INSERT OR UPDATE OR DELETE ON assemblies_parts
FOR EACH ROW                              
EXECUTE PROCEDURE sum_assembly_part_subtotal();

This seems to work OK. I am new to functions and triggers so please criticize any errors.

But I also need this subtotal to be recalculated when the products table price is updated. I can't seem to get a suitable set of subqueries put together for this.

\d products
                                           Table "public.products"
       Column        |          Type          |                           Modifiers                 
          
---------------------+------------------------+-----------------------------------------------------
----------
 product_id          | integer                | not null default nextval('products_product_id_seq'::
regclass)
 product_description | text                   | 
 sku                 | character varying(100) | 
 up_date             | date                   | not null default now()
 class               | character varying(100) | 
 subclass            | character varying(100) | 
 vendor_id           | integer                | not null
 vendor_name         | character varying(100) | not null
 price               | numeric(10,2)          | not null
 model               | character varying(100) | 
 product_notes       | text                   | 
 check_days          | text                   | not null default '2'::text
 product_url         | text                   | 
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
Foreign-key constraints:
    "products_vendor_id_fkey" FOREIGN KEY (vendor_id) REFERENCES vendors(vendor_id)
    "products_vendor_name_fkey" FOREIGN KEY (vendor_name) REFERENCES vendors(vendor_name)
Referenced by:
    TABLE "assemblies_parts" CONSTRAINT "assemblies_parts_product_id_fkey" FOREIGN KEY (assembly_par
t_product_id) REFERENCES products(product_id)

\d assemblies_parts
                                             Table "public.assemblies_parts"
          Column           |     Type      |                                  Modifiers             
                     
---------------------------+---------------+--------------------------------------------------------
---------------------
 assembly_part_id          | integer       | not null default nextval('assemblies_parts_assembly_par
t_id_seq'::regclass)
 assembly_part_assembly_id | integer       | not null
 assembly_part_name        | text          | not null
 assembly_part_description | text          | 
 assembly_part_class       | text          | 
 assembly_part_subclass    | text          | 
 assembly_part_notes       | text          | 
 assembly_part_url         | text          | 
 assembly_part_quantity    | integer       | not null
 assembly_part_product_id  | integer       | not null
 assembly_part_subtotal    | numeric(10,2) | not null
 assembly_part_update      | date          | not null default now()
Indexes:
    "assemblies_parts_pkey" PRIMARY KEY, btree (assembly_part_id)
Foreign-key constraints:
    "assemblies_parts_product_id_fkey" FOREIGN KEY (assembly_part_product_id) REFERENCES products(pr
oduct_id)
    "assembly_part_assembly_id_fkey" FOREIGN KEY (assembly_part_assembly_id) REFERENCES assemblies(a
ssembly_id)
Triggers:
    sum_assembly_part_subtotal_trigger BEFORE INSERT OR DELETE OR UPDATE ON assemblies_parts FOR EAC
H ROW EXECUTE PROCEDURE sum_assembly_part_subtotal()

 

[MrCBofBCinTX]

OK, seem to have it down:

CREATE FUNCTION sum_assembly_part_subtotal_update() RETURNS "trigger" AS '
DECLARE assembly_part_subtotal_new numeric(10,2);
BEGIN assembly_part_subtotal_new := 
(SELECT assembly_part_quantity FROM assemblies_parts WHERE assembly_part_product_id=OLD.product_id)*
(SELECT price FROM products WHERE product_id=OLD.product_id);
UPDATE assemblies_parts set assembly_part_subtotal=assembly_part_subtotal_new WHERE assembly_part_product_id=OLD.product_id;
RETURN NULL;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER sum_assembly_part_subtotal_update_trigger
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE PROCEDURE sum_assembly_part_subtotal_update();

 

[MrCBofBCinTX]

Well of course this didn't keep working once I started filling in real data sets. As soon as the other script did its automatic updates, I got multiple values for subquery, since the same product_id was used several times.

This seems to work OK:

CREATE FUNCTION sum_assembly_part_subtotal_update2() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
assembly_part_subtotal_new2 numeric(10,2);
assembly_part_id_set integer[];
i integer;
BEGIN
SELECT array(SELECT assembly_part_id FROM assemblies_parts WHERE assembly_part_product_id=OLD.product_id) INTO assembly_part_id_set;
i := 1;
LOOP
IF assembly_part_id_set[i] ISNULL THEN
EXIT;--Exit when no more values in array
END IF;
assembly_part_subtotal_new2 := 
(SELECT assembly_part_quantity FROM assemblies_parts WHERE assembly_part_id=assembly_part_id_set[i])*
(SELECT price FROM products WHERE product_id=OLD.product_id);
UPDATE assemblies_parts set assembly_part_subtotal=assembly_part_subtotal_new2 WHERE assembly_part_id=
assembly_part_id_set[i];
i := i + 1;
END LOOP;
RETURN NULL;
END;$$;

and

CREATE TRIGGER sum_assembly_part_subtotal_update2_trigger AFTER UPDATE ON products FOR EACH ROW EXECUTE PROCEDURE sum_assembly_part_subtotal_update2();