PostgreSQL create trigger: Syntax error on insert action

Question

I want to create a trigger in PostgreSQL.

I have a products table and a categories table. If a new tuple is inserted into the products table with a category_id, which does not exist yet, I want to create a category with this exact category_id. I wrote some SQL code but I get a syntax error at "INSERT" at line 6.

My code is the following:

CREATE TRIGGER ProductTrig
  AFTER INSERT ON products
  REFERENCING NEW ROW AS newrow
  FOR EACH ROW
  WHEN (newrow.category_id NOT IN (SELECT category_id FROM categories))
  INSERT INTO categories (category_id) VALUES (newrow.category_id);

Does anybody see the problem? Thanks for your help!

https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE — , May 25 '20 at 20:31
There are a couple problems here. Firstly, you can't use an INSERT statement directly in a trigger. You will need to create a trigger function first, and then get the trigger to call that. Also, you can't use subqueries in your WHEN clause expression. — Thom Brown, May 25 '20 at 20:38

score 1 · Answer 1 · edited May 25 '20 at 20:38

1

From the documentation for CREATE TRIGGER, you can see that the end of the statement must be EXECUTE PROCEDURE followed by a stored procedure name. In your query you specify an INSERT clause, which does not meet these requirements.

You'll need to frame your INSERT clause within a stored procedure and then refer to that at the end of your CREATE TRIGGER statement. The documentation for CREATE PROCEDURE can help you there.

edited May 25 '20 at 20:38

answered May 25 '20 at 20:35

Rob Streeting

1,675
3
16
27

CREATE FUNCTION insert_data() RETURNS trigger AS $BODY$ BEGIN IF NOT EXISTS (SELECT FROM categories c WHERE c.category_id = OLD.category_id) THEN INSERT INTO categories(category_id, category_name) VALUES (OLD.category_id, 'Unknown'); END IF; RETURN NEW; END; $BODY$ LANGUAGE plpgsql; CREATE TRIGGER ProductTrig BEFORE INSERT ON products FOR EACH ROW EXECUTE PROCEDURE insert_data(); When I now insert a product with a catID that does not exist, I get an error saying that the key is not in categories so I cannot create a product with this foreign key – asdf May 26 '20 at 12:14

score 0 · Answer 2 · edited Aug 31 '20 at 08:44

As you can see in the docs, write a function to run after the trigger.

For example:

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

PostgreSQL create trigger: Syntax error on insert action

2 Answers2