Question 255820 in PostgreSQL Question posted in
The official documentation can be found here.

Creating trigger in Postgresql not working

I’m trying to create a trigger in Postgresql, but the delimiter $$ isn’t recongnized. I’have small problems with the syntax of Postgresql after moving from MySql, so excuse my if this is just a very basic question.

I tried this code

CREATE TRIGGER trigger_name AFTER INSERT ON my_table FOR EACH ROW
$$
BEGIN
  //Execute statement
END;
$$
LANGUAGE plpgsql;

But the error is

syntax error at or near "$$
BEGIN
  //Execute statement
END;
$$"
Tags:

2

Answers


  1. 0

    When developing triggers in PostgreSQL, you are not required to use $$ delimiters. Instead, specify the trigger independently and create your trigger logic using plpgsql functions and the $$ delimiter. Try the code given under, I updated your code:

    -- Define your trigger
CREATE TRIGGER trigger_name
AFTER INSERT ON my_table
FOR EACH ROW
EXECUTE FUNCTION your_function();

-- Define your PL/pgSQL function
CREATE OR REPLACE FUNCTION your_function() RETURNS TRIGGER AS $$
BEGIN
  -- Your trigger logic here
  -- You can use NEW to access the newly inserted row

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    Here is the other way how you can achieve the same functionalty of trigger by first creating the function and then trigger.

    -- Define the function

CREATE OR REPLACE FUNCTION your_function() RETURNS TRIGGER AS $$
BEGIN
 -- Your Trigger logic here


 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create or replace the trigger

CREATE TRIGGER trigger_name
AFTER INSERT ON my_table
FOR EACH ROW
EXECUTE FUNCTION your_function();

    In order to avoid function doesn’t exist error, you can run this code.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search