Re: Multiple row update with trigger - Mailing list pgsql-novice

Derrick Betts wrote:
> I have a table with a primary key for each row, and a group
> identification number (groupid) which is not necessarily unique, for
> each row.  As such, I may have 3-5 rows with the same groupid.
> Anytime a row is updated, I need a trigger to update any other rows with
> the same groupid as the NEW row that is being updated.
> For example, rows 1, 2 & 3 all share the same groupid:
> Anytime row 1 is updated, I need row 2 and 3 updated with the same
> information as row 1.
> Anytime row 2 is updated, I need row 1 and 3 updated with the same
> information as row 2.
> Anytime row 3 is updated, I need row 1 and 2 updated with the same
> information as row 3.
>
> I would like to use a trigger, but the only way I can see updating the
> "additional" rows is with the NEW variable, and this is only visible on
> a FOR EACH ROW trigger.  This causes problems in that the trigger will
> get caught in an infinite loop as it begins updating the additional rows.
>
>
I solved the problem.  I added a placeholder column in the table that
gets updated to keep the trigger from firing every time. The trigger and
trigger function are outlined below.  The placeholder column is called
upd and it never gets changed inside the table itself, it just looks
like it is with the trigger logic.

CREATE OR REPLACE FUNCTION update_cobor_summary()
   RETURNS "trigger" AS
$BODY$
DECLARE
rec RECORD;

BEGIN
IF new.upd IS NOT NULL THEN new.upd = true; END IF;
IF (new.upd <> old.upd) THEN
   FOR rec IN SELECT contactid FROM contact
    WHERE multigroupid = (SELECT multigroupid FROM contact WHERE contactid
= New.contactid)
     LOOP
       UPDATE contact_app_summary SET
         propval = new.propval,
         occupancy = new.occupancy,
         purpose = new.purpose,
         saleval = new.saleval,
         upd = NULL
     WHERE contactid = rec.contactid;
   END LOOP;
   RETURN NULL;
ELSE
  new.upd = old.upd;
END IF;

RETURN NEW;
END;
$BODY$
   LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER cobor_upd_summary BEFORE UPDATE
    ON contact_app_summary FOR EACH ROW
    EXECUTE PROCEDURE update_cobor_summary();

The contact table looks like this:
CREATE TABLE "101".contact
(
   contactid int4 NOT NULL DEFAULT
nextval(('seq_contactid'::text)::regclass),
   firstname varchar,
   lastname varchar,
   hphone varchar,
   wphone varchar,
   cphone varchar,
   fphone varchar,
   email varchar,
   passwd varchar,
   uname varchar,
   category varchar(3) DEFAULT '0'::character varying,
   filter varchar(60) DEFAULT '0'::character varying,
   subfilter varchar DEFAULT 'All'::character varying,
   last_login timestamp DEFAULT now(),
   multigroupid int4 DEFAULT nextval('seq_multigroupid'::regclass), --
This is used to link all the co-borrower groups together.
   CONSTRAINT pk_contacts PRIMARY KEY (contactid)
)
WITH OIDS;