trigger product stock

Question

I created a trigger that allows me each time the hen I add a lot to the table amount he adds this amount in the article table, but my error says that the registration (new) items.

CREATE TABLE article (
  "idArticle" integer NOT NULL,
  "desArticle" character varying,
  "famiArticle" integer,
  "photoArticle" character varying,
  "stkPhArticle" integer,
  "stkThArticle" integer,
  "seuSurArticle" integer,
  "seuAlrArticle" integer,
  "seuReapArticle" integer,
  "phtAchaArticle" double precision,
  "phtVentArticle" double precision,
  "pttcArticle" double precision,
  "tvaArticle" integer,
  CONSTRAINT pk_idarticle PRIMARY KEY ("idArticle"),
  CONSTRAINT fk_famiarticle FOREIGN KEY ("famiArticle")
      REFERENCES famillearticle (idfa) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_tvaarticle FOREIGN KEY ("tvaArticle")
      REFERENCES tvaarticle (idtva) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE lot (
  "idLot" integer NOT NULL,
  "idArticle" integer NOT NULL,
  "emplacementLot" integer NOT NULL,
  "qteLot" integer NOT NULL,
  "dlcLot" date NOT NULL,
  CONSTRAINT pk_idlot PRIMARY KEY ("idLot"),
  CONSTRAINT fk_emplacementlot FOREIGN KEY ("emplacementLot")
      REFERENCES etagere ("idEtag") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_idarticle FOREIGN KEY ("idArticle")
      REFERENCES article ("idArticle") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Trigger and function:

CREATE OR REPLACE FUNCTION ajouter_lot_article()
      RETURNS trigger AS
    $BODY$
        BEGIN

        UPDATE article SET stkThArticle = stkThArticle + NEW.qteLot
        WHERE article.idArticle = New.idArticle;

        END;
    $BODY$
      LANGUAGE plpgsql;

CREATE TRIGGER apres_ajout_lot
  AFTER INSERT
  ON lot
  FOR EACH ROW
  EXECUTE PROCEDURE ajouter_lot_article();

erreur: "NEW" n'a pas de champs "idarticle"

score 1 · Answer 1 · answered Mar 06 '15 at 11:13

Column names are case sensitive, so you need to put the columns in your trigger inside double quotes.

 CREATE OR REPLACE FUNCTION ajouter_lot_article()
      RETURNS trigger AS
    $BODY$
        BEGIN

        UPDATE article SET "stkThArticle" = "stkThArticle" + NEW."qteLot"
        WHERE article."idArticle" = New."idArticle";

        END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    ALTER FUNCTION ajouter_lot_article()
      OWNER TO postgres;

trigger product stock

1 Answers1