"Syntax error at or near SET" on trigger create

Question

I have two tables (stock and productsLot) and I need a trigger that calculates the total quantity of a product(in stock table) by summing the lot quantities(from productsLot).

I don't know much about triggers but my problem is similar to this one Calculate column value from another column in another table. Here's my case.

CREATE TABLE Stock (productId INTEGER,totalQuantity INTEGER, price NUMERIC(30,2))
PRIMARY KEY (productId); 

CREATE TABLE productsLot (productId INTEGER CONSTRAINT fk_stock_productsLot REFERENCES ON Stock(productId) , lotNumber VARCHAR, lotQuantity INTEGER, expirationDate DATE, PRIMARY KEY (productId, lotNumber)); 

CREATE TRIGGER update_quantity_stock
BEFORE INSERT ON  stock SET NEW.totalQuantity = 
  (
    SELECT sum(lotQuantity)
      FROM productsLot
     WHERE productId = NEW.productId
     LIMIT 1
  );

I'm getting this error:

syntax error at or near "SET"

The question you linked is for MySQL triggers. In Postgres, you need to write a trigger procedure. There are examples in the docs: https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE — Nick Barnes, Jun 13 '19 at 11:12

score 0 · Answer 1 · answered Jun 13 '19 at 12:28

Quite a foolish question apparently but I got it thanks to Nick. Here's the solution

CREATE OR REPLACE FUNCTION update_quantity_stock()
    RETURNS trigger AS
  $$
  BEGIN
           UPDATE stock SET totalQuantity = (select sum(lotQuantity)
        FROM productsLot
       WHERE productsLot.productId=stock.productId);

      RETURN NEW;
  END;
  $$
  LANGUAGE 'plpgsql';

-- trigger

CREATE TRIGGER update_quantity_stock_trigger
   AFTER INSERT
   ON productsLot
   FOR EACH ROW
   EXECUTE PROCEDURE update_quantity_stock();

"Syntax error at or near SET" on trigger create

1 Answers1