CODEWITHSUNDEEP
postgresqltriggers
ImZ
ImZ

Reputation: 46

Error trigger. Trigger without return. PostgreSQL

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()

Upvotes: 0

Views: 5645

Answers (2)

Anthony Sotolongo
Anthony Sotolongo

Reputation: 1648

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

Upvotes: 1

Arkhena
Arkhena

Reputation: 280

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();

Upvotes: 2

Related Questions