why does my trigger function cause a null error?-CodePudding

PostgreSQL. My trigger func:

CREATE OR REPLACE FUNCTION new_purchase() RETURNS TRIGGER AS $psql$
BEGIN
    IF NOT EXISTS(
        SELECT 1
        FROM currents c
        WHERE c.name = new.name
    )
    THEN
        INSERT INTO currents (name, qty)
        VALUES (new.name, new.qty);
    ELSE
        UPDATE currents
        SET
            qty = qty   new.qty
        WHERE
            name = new.name;
    END IF;
    return new;
END;
$psql$ language plpgsql;

Then my trigger:

create trigger new_purchase_trigger 
after insert on purchases 
for each statement 
execute procedure new_purchase();

my two tables with which i am manipulating: Purchases

CREATE TABLE purchases (
  name VARCHAR,
  date TIMESTAMP,
  qty INTEGER,
  price NUMERIC,
  about VARCHAR
)

Currents

CREATE TABLE currents (
  name VARCHAR,
  qty INTEGER
)

My INSERT:

INSERT INTO purchases (name, date, qty, price, about)
VALUES ('cheese', '2022-07-31', 1000, 11500, 'holland');

And finally the error when i am adding data to purchases via INSERT INTO - VALUES:

ERROR: NULL value in column "name" of relation "currents" violates the NOT NULL constraint
DETAIL: Error string contains (null, null).
CONTEXT: SQL statement: "INSERT INTO currents (name, qty)
VALUES(new.name, new.qty)"
PL/pgSQL new_purchase() function, line 9, statement SQL statement SQL state: 23502

CodePudding user response：

You need a ROW level trigger for what you are trying to achieve. In a statement level trigger (for each statement) the record new is not populated:

create trigger new_purchase_trigger 
after insert on purchases 
for each row
execute procedure new_purchase();

Note that you can simplify your trigger function if you declare currents.name as the primary key:

CREATE OR REPLACE FUNCTION new_purchase() 
RETURNS TRIGGER 
AS $psql$
BEGIN
  INSERT INTO currents (name, qty)
  VALUES (new.name, new.qty)
  on conflict (name) do update
    set qty = currents.qty   excluded.qty;
  return new;
END;
$psql$ 
language plpgsql;