CODEWITHSUNDEEP
sqlpostgresqlpostgresql-triggers
asdf
asdf

Reputation: 11

PostgreSQL create trigger: Syntax error on insert action

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!

Upvotes: 0

Views: 933

Answers (2)

mdmundo
mdmundo

Reputation: 2276

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

Upvotes: 0

Rob Streeting
Rob Streeting

Reputation: 1735

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.

Upvotes: 1

Related Questions