CODEWITHSUNDEEP
postgresqltriggers
Denis
Denis

Reputation: 1555

tuple to be updated was already modified by an operation triggered by the current command

I just added a new AFTER trigger to the database, and I am currently trying to update a new column for the existing table with some computed boolean.

However I am getting this error:

ERROR: tuple to be updated was already modified by an operation triggered by the current command
HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.

I understand that this somehow has to do with new AFTER trigger I added, but I took care to disable it, so I don't why this would happen... As it didn't helped, I completely deleted the trigger, but I am still not able to update the table:

CREATE TABLE public.extractions_2
(
    id bigint NOT NULL DEFAULT nextval('extractions_2_id_seq'::regclass),
    settling_bank text,
    created_at timestamp without time zone,
    is_prehedge boolean,
    --many more irrelevant columns...
    CONSTRAINT extractions_2_pkey PRIMARY KEY (id),
    CONSTRAINT extractions_2_filecode_created_at_key UNIQUE (filecode, created_at)
)
WITH (
  OIDS=FALSE
);

-- Trigger: set_is_prehedge on public.extractions_2

-- DROP TRIGGER set_is_prehedge ON public.extractions_2;

CREATE TRIGGER set_is_prehedge
  AFTER INSERT
  ON public.extractions_2
  FOR EACH ROW
  EXECUTE PROCEDURE public.set_is_prehedge();
ALTER TABLE public.extractions_2 DISABLE TRIGGER set_is_prehedge;

CREATE OR REPLACE FUNCTION public.set_is_prehedge()
  RETURNS trigger AS
$BODY$
begin
update NEW
set NEW.is_prehedge= case when(NEW.settling_bank LIKE '%PRE HEDGE%') THEN true ELSE false END;
return NEW;
end;

I am trying to update the table simply as:

update extractions_2 set 
is_prehedge = case when(settling_bank LIKE '%PRE HEDGE%') THEN true ELSE false END;

... what's wrong?

ANSWER: I needed a BEFORE trigger; moreover, I had an other erroneous BEFORE ON UPDATE trigger, which would prevent me from updating fields, and which I didn't posted here becaue I thought it was irrelevant (it was only irrelevant because I never updated the table before, only inserted)

Upvotes: 1

Views: 6251

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247495

You want a BEFORE INSERT trigger for that.

Instead of trying to UPDATE something, all you have to do is assign the appropriate value to NEW:

CREATE OR REPLACE FUNCTION public.set_is_prehedge()
  RETURNS trigger LANGUAGE plpgsql AS
$$BEGIN
   NEW.is_prehedge := (NEW.settling_bank LIKE '%PRE HEDGE%');
   RETURN NEW;
END;$$;

Upvotes: 2

Related Questions