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!

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
– Thom Brown, Commented May 25, 2020 at 20:38

Rob Streeting · Accepted Answer · 2020-05-25 20:38:50Z

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, 2020 at 20:38

user330315

answered May 25, 2020 at 20:35

Rob Streeting

1,7253 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

asdf Over a year ago

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

mdmundo · Accepted Answer · 2020-08-31 08:44:00Z

0

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

edited Aug 31, 2020 at 8:44

user330315

answered May 25, 2020 at 20:47

mdmundo

2,2844 gold badges26 silver badges40 bronze badges

Collectives™ on Stack Overflow

PostgreSQL create trigger: Syntax error on insert action

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related