I have 2 tables:

CREATE TABLE  IF NOT EXISTS  supply_history(
  id_supply int GENERATED ALWAYS AS IDENTITY,
  id_book int NOT NULL,
  title varchar(150) DEFAULT('None'),
  quantity int NOT NULL,

  PRIMARY KEY (id_supply),
  CONSTRAINT fk_id_book
      FOREIGN KEY (id_book)
          REFERENCES books(id_book)
);

And:

CREATE TABLE  IF NOT EXISTS  quantity_books(
  id_quantity int GENERATED ALWAYS AS IDENTITY,
  id_book int NOT NULL,
  quantity int NOT NULL,

  PRIMARY KEY (id_quantity),
  CONSTRAINT fk_id_book
      FOREIGN KEY (id_book)
          REFERENCES books(id_book)
);

I would like to sum quantity from quantity_books and supply_history using trigger. Field quantity from quantity_books contains some integer, and after insertion to supply table I would like to update by suming up quantities fields from both tables and save it in quantity_books.

My trigger looks like:

-- Trigger to update quantity of books after supply
DROP FUNCTION IF EXISTS update_quantity_books_after_supply() CASCADE;
CREATE OR REPLACE FUNCTION update_quantity_books_after_supply()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $trigger$
    BEGIN
        UPDATE quantity_books AS q
            SET NEW.quantity = supply_history.quantity   OLD.quantity
            FROM supply_history
            WHERE q.id_book = supply_history.id_book;
        RETURN NEW;
    END;
$trigger$;

DROP TRIGGER IF EXISTS trigger_completion_date ON supply_history;
CREATE TRIGGER trigger_insert_supply
   AFTER INSERT
   ON supply_history
   FOR EACH ROW
       EXECUTE FUNCTION update_quantity_books_after_supply();

And triggers by insertion:

CALL insert_vals_supply(5, null, 200);

The problem is with "set" line, tried different combinations with OLD and NEW keywords but didnt work.

Thanks for help

CodePudding user response：

You have access to the updated row via NEW, so there's no need to do an update...from referencing again the whole table this row goes into. Additionally, you need to handle delete, update and truncate on that table - otherwise it'll go out of sync if you watch insert operations only.

Luckily, you can handle insert, update and delete events in a single trigger:

DROP FUNCTION IF EXISTS maintain_quantity_books_trigger_function() CASCADE;
CREATE OR REPLACE FUNCTION maintain_quantity_books_trigger_function()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $trigger$
    BEGIN
        UPDATE quantity_books AS q
            SET quantity = q.quantity 
                           - coalesce(OLD.quantity,0)
                             coalesce(NEW.quantity,0)
            WHERE q.id_book = coalesce(NEW.id_book,OLD.id_book);
        RETURN null; --it's an after trigger, so this value is ignored
    END;
$trigger$;

DROP TRIGGER IF EXISTS maintain_quantity_books_trigger ON supply_history;
CREATE TRIGGER maintain_quantity_books_trigger
AFTER INSERT OR UPDATE OF quantity OR DELETE ON supply_history
FOR EACH ROW EXECUTE FUNCTION maintain_quantity_books_trigger_function();

It's possible even without checking TG_OP thanks to coalesce() and the behaviour or visibility of OLD and NEW:

1. OLD will be undefined (null) for an INSERT trigger, so it won't perform subtraction that's otherwise necessary in case you're doing a DELETE or UPDATE.
2. In case of UPDATE both OLD and NEW are defined, so you can substract one and add the other, to reflect the correction applied by that update.
3. DELETE won't see a NEW record, so it will only subtract the quantity it removes, without adding anything.

TRUNCATE trigger cannot be declared FOR EACH ROW with the rest of events, so it has to be defined separately:

DROP FUNCTION IF EXISTS maintain_quantity_books_truncate_trigger_function() CASCADE;
CREATE OR REPLACE FUNCTION maintain_quantity_books_truncate_trigger_function() 
RETURNS TRIGGER LANGUAGE plpgsql AS $trigger$
    BEGIN
      UPDATE quantity_books AS q
      SET quantity = 0;
      RETURN NULL;
    END; 
$trigger$;

DROP TRIGGER IF EXISTS maintain_quantity_books_truncate_trigger ON supply_history;
CREATE TRIGGER maintain_quantity_books_truncate_trigger 
AFTER TRUNCATE ON supply_history
EXECUTE PROCEDURE maintain_quantity_books_truncate_trigger_function();

Online demo