Error trigger. Trigger without return. PostgreSQL

Question

I created trigger and function.

CREATE TRIGGER trigger_update_quantity AFTER INSERT ON orderitem
    FOR EACH ROW EXECUTE PROCEDURE update_quantity();

        CREATE OR REPLACE FUNCTION update_quantity() RETURNS TRIGGER AS $quantity_update$
          DECLARE
            flower INT;
          BEGIN
            flower = New.flower_num;
            UPDATE product SET quantity = quantity - New.quantity
            WHERE flower_num = flower;
          END;
          $quantity_update$
        LANGUAGE plpgsql;

When I try to insert a row into a table. Everything i have the following error.

INSERT INTO "public"."orderitem" ("order_num", "flower_num", "quantity", "total_price") VALUES (?, ?, ?, ?);

            [2F005] ERROR: control reached end of trigger procedure without RETURN
            Where: PL/pgSQL function update_quantity()

I might be mistaken but I think a trigger function should return values he mlust use for his action (here INSERT). So in your example, I would return the NEW value. — Arkhena
– Arkhena, Commented Jul 27, 2017 at 11:50

Arkhena · Accepted Answer · 2017-07-27 12:34:19Z

2

Here's what I would write (not tested):

    CREATE OR REPLACE FUNCTION update_quantity() RETURNS TRIGGER AS $quantity_update$
      DECLARE
        flower INT;
      BEGIN
        flower = New.flower_num;
        UPDATE product SET quantity = quantity - New.quantity
        WHERE flower_num = flower;
        RETURN NEW;
      END;
      $quantity_update$
    LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_quantity AFTER INSERT ON orderitem
    FOR EACH ROW EXECUTE PROCEDURE update_quantity();

answered Jul 27, 2017 at 12:34

Arkhena

2802 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anthony Sotolongo · Accepted Answer · 2017-07-28 20:56:34Z

1

how is AFTER TRIGGER, you can return RETURN OLD; or RETURN NULL;

answered Jul 28, 2017 at 20:56

Anthony Sotolongo

1,6882 gold badges13 silver badges23 bronze badges

Collectives™ on Stack Overflow

Error trigger. Trigger without return. PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related