CREATE TABLE t_product
(
  id_product bigserial NOT NULL,
  code character varying(4),
  "name" character varying(50) NOT NULL,
  description character varying(250) NOT NULL,
  price_ht double precision NOT NULL,
  stock_web integer NOT NULL,
  picture_id integer,
  category_id integer NOT NULL,
  CONSTRAINT id_product PRIMARY KEY (id_product),
  CONSTRAINT category_idep FOREIGN KEY (category_id)
      REFERENCES t_category (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT picture_idp FOREIGN KEY (picture_id)
      REFERENCES t_picture (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t_product OWNER TO postgres;

 CREATE TABLE t_order
(
  id serial NOT NULL,
  create_date date NOT NULL,
  price_ht double precision NOT NULL,
  user_id integer NOT NULL,
  CONSTRAINT order_id PRIMARY KEY (id),
  CONSTRAINT user_ido FOREIGN KEY (user_id)
      REFERENCES t_user (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE

 CREATE TABLE t_order_product
(
  id serial NOT NULL,
  product_id integer NOT NULL,
  quantity integer,
  price_ht double precision NOT NULL,
  order_id integer NOT NULL,
  CONSTRAINT order_product_id PRIMARY KEY (id),
  CONSTRAINT order_idop FOREIGN KEY (order_id)
      REFERENCES t_order (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT product_idop FOREIGN KEY (product_id)
      REFERENCES t_product (id_product) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t_order_product OWNER TO postgres;

 DROP FUNCTION gest_stock()cascade;
CREATE OR REPLACE FUNCTION gest_stock()
  RETURNS opaque AS
$BODY$
	declare stock varchar;
	declare	id_prod integer;
	declare	q integer;
	begin
		select into q new.quantity
		where id_prod = new.product_id;
	update t_product
			set stock_web = stock_web + q 
			where id_product=id_prod;
		q = 0;
		select into q old.quantity 
		where id_prod = product_id;

	update t_product
			set stock_web = stock_web - q 
			where id_product=id_prod;
	return null;	
	end;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION gest_stock() OWNER TO postgres;

CREATE TRIGGER gestion_stock
  after INSERT or delete or update
  ON t_order_product
  FOR EACH ROW
  EXECUTE PROCEDURE gest_stock();