Re: Multiple row update with trigger

[Derrick Betts <list(at)blueaxis(dot)com>]

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;