CODEWITHSUNDEEP
postgresqltriggersinsert
Zain Virani
Zain Virani

Reputation: 263

Trigger error: Record "new" is not assigned yet

I have the following code in postgresql:

CREATE OR REPLACE FUNCTION update_category() RETURNS trigger AS $newProduct$
BEGIN
    UPDATE category SET product_count = product_count + 1 WHERE cat_id = NEW.cat_id;
    INSERT INTO notification (content, type, p_id) VALUES('new product', 1, NEW.p_id);
    RETURN NEW;
END;
$newProduct$ LANGUAGE plpgsql;

CREATE TRIGGER update_cat AFTER INSERT ON product
    EXECUTE PROCEDURE update_category();

And after inserting a record into product I get the error:

[2017-03-20 16:05:05] [55000] ERROR: record "new" is not assigned yet
[2017-03-20 16:05:05] Detail: The tuple structure of a not-yet-assigned record is indeterminate.
[2017-03-20 16:05:05] Where: SQL statement "UPDATE category SET product_count = product_count + 1 WHERE cat_id = NEW.cat_id"
[2017-03-20 16:05:05] PL/pgSQL function update_category() line 3 at SQL statement

I've looked around for a solution, but I only find cases where the error is because

FOR EACH STATEMENT

is being used instead of

FOR EACH ROW

Seeing as I'm simply executing the procedure once, the solution doesn't apply in my case.

Thanks for your help!

Upvotes: 9

Views: 11439

Answers (1)

Zain Virani
Zain Virani

Reputation: 263

The solution was indeed to add FOR EACH ROW:

CREATE TRIGGER update_cat AFTER INSERT ON product
    FOR EACH ROW EXECUTE PROCEDURE update_category();

I assumed FOR EACH ROW meant calling the procedure once for each row in product, not for each row inserted.

Upvotes: 8

Related Questions