Link to home
Create AccountLog in
Avatar of Mazdajai
MazdajaiFlag for United States of America

asked on

postgres on update cacade

We want to automatically update TableB (products) ColumnX when we add value into TableA (order_items) ColumnX.

I have added 'ON UPDATE CASCADE ON DELETE RESTRICT' for the table but it does updating. Any ideas?

CREATE TABLE order_items
(
  product_no integer NOT NULL,
  order_id integer NOT NULL,
  quantity integer,
  CONSTRAINT order_items_pkey PRIMARY KEY (product_no, order_id),
  CONSTRAINT order_items_order_id_fkey FOREIGN KEY (order_id)
      REFERENCES orders (order_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION,
  CONSTRAINT order_items_product_no_fkey FOREIGN KEY (product_no)
      REFERENCES products (product_no) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE RESTRICT
)
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
)

Open in new window

Avatar of awking00
awking00
Flag of United States of America image

Probably the easiest way is to create an after insert on TableA trigger to do the update.
Avatar of Mazdajai

ASKER

Where do I need to add the after insert trigger in my sql code?
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Thanks. I will give this a shot and let you know.