How to cancel an INSERT operation in a PostgreSQL Database from within a BEFORE-INSERT TRIGGER (Have done this in MySQL)

Question

I made some work in MySQL where I did put a lot of business logic in the Back-end using TRIGGERS, I have to migrate to PostgreSQL and unfortunately the trigger mechanisms are not the same. So far I think I have to recode them all manually.

Here is a small issue : in a Postgresql trigger that executes before insert How do I cancel the insert operation from within the trigger function?

Example: I have a table with 2 fields (id,field_a), I have a trigger that executes in before inserting a row in this table.

Just to illustrate this I cancel this insert from within the trigger if the field_a = 5.

So in MySQL I did this(Raised a custom exception) and it worked (stopped the insert and showed a custom message) :

CREATE TABLE `mydatabase`.`mytable` (
  `id` int(11) DEFAULT '0',
  `field_a` int(11) DEFAULT '0',
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC ;

CREATE DEFINER=`username`@`localhost` TRIGGER `the_trigger_in_mysql` BEFORE INSERT ON `mytable` FOR EACH ROW 

BEGIN
IF NEW.a_field = 5 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Please choose another number!!';
END IF;
END;

I am trying to do the same here for Postgresql , found some new stuff reading about Pgplsql and all

 CREATE TABLE "public"."mytable" (
      "id" int4 DEFAULT '0',
      "field_a"  int4 DEFAULT '0',
       CONSTRAINT "mytable__pkey" PRIMARY KEY ("id")
    ) ;
    
   ALTER TABLE "public"."log" 
  OWNER TO "username";

CREATE TRIGGER "the_trigger_in_postgresql" BEFORE INSERT ON "public"."mytable"
FOR EACH ROW
EXECUTE PROCEDURE "public"."the_trigger_in_postgresql_function"();

CREATE OR REPLACE FUNCTION "public"."the_trigger_in_postgresql_function"()
  RETURNS "pg_catalog"."trigger" AS $BODY$BEGIN 
    IF NEW.a_field = 5 THEN
        RAISE DEBUG 'Please choose another number!!';
    END IF;
RETURN NEW;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION "public"."the_trigger_in_postgresql_function"() OWNER TO "username";

Unfortunately it doesn't work! Any thoughts about this?

Read though https://stackoverflow.com/q/22733254/2864740 and https://dba.stackexchange.com/q/76333 and https://www.postgresql.org/docs/current/plpgsql-trigger.html? — user2864740, Jul 19 '20 at 01:30
yup should have used raise exception instead of raise debug (same logic works) , Thanks again — Aness, Jul 19 '20 at 01:38

score 1 · Accepted Answer · answered Jul 19 '20 at 01:37

1

So As pointed in the link by @user2864740 , the Idea was good same as MySQL raising an exception but I was wrong in the spelling the code must be

RAISE EXCEPTION 'Please choose another number!!';

instead of raise debug in the TRIGGER body function

answered Jul 19 '20 at 01:37

Aness

610
1
7
24

How to cancel an INSERT operation in a PostgreSQL Database from within a BEFORE-INSERT TRIGGER (Have done this in MySQL)

1 Answers1