×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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:

CODE

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;

CODE

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.


CODE

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

CODE

\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()

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

(OP)
OK, seem to have it down:

CODE

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;

CODE

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

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

(OP)
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:

CODE

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close