Thread: Multiple row update with trigger

Multiple row update with trigger

From
Derrick Betts
Date:
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.

The other alternative is a FOR EACH STATEMENT trigger.  However, the
values that are being updated in the NEW row aren't visible to this type
of trigger to allow me to update the other rows.

Does anyone have any good ideas as to how I might solve this problem?
Perhaps even taking a completely different approach in some way?
Thanks,
Derrick


Re: Multiple row update with trigger

From
Andreas Kretschmer
Date:
Derrick Betts <list@blueaxis.com> schrieb:
> Does anyone have any good ideas as to how I might solve this problem?
> Perhaps even taking a completely different approach in some way?

Maybe with a RULE instead a TRIGGER. A Rule, with DO INSTEAD. For
example, a RULE like

create or replace rule my_rule as on update to my_table do instead update
my_table ...


I'm not sure, try it.
http://www.postgresql.org/docs/current/interactive/rules-update.html


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Multiple row update with trigger

From
Jon Sime
Date:
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 don't have a direct answer to the question you asked, but I am
wondering...

What's the actual reason for having data duplicated within the same
table like this? From what you've said so far, it just sounds like you
have a table that is improperly denormalized and you're trying to hack
something on top of the design to fix what should be solved by
normalizing the data in the table.

Does the PK contain significant data, or is it arbitrary (e.g. a
sequence)? If the latter, it really sounds like you should be using this
groupid column as your PK and get rid of the current PK column -- or at
the very least, put a unique constraint/index on the groupid column.

If the former, my guess is that you should still be using the groupid as
the PK and what you currently have as the PK should instead be in a
separate table that allows you to do a 1-to-many groupid-formerPK
relationship.

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/

Re: Multiple row update with trigger

From
Derrick Betts
Date:
Jon Sime wrote:
> 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 don't have a direct answer to the question you asked, but I am
> wondering...
>
> What's the actual reason for having data duplicated within the same
> table like this? From what you've said so far, it just sounds like you
> have a table that is improperly denormalized and you're trying to hack
> something on top of the design to fix what should be solved by
> normalizing the data in the table.
>
> Does the PK contain significant data, or is it arbitrary (e.g. a
> sequence)? If the latter, it really sounds like you should be using this
> groupid column as your PK and get rid of the current PK column -- or at
> the very least, put a unique constraint/index on the groupid column.
>
> If the former, my guess is that you should still be using the groupid as
> the PK and what you currently have as the PK should instead be in a
> separate table that allows you to do a 1-to-many groupid-formerPK
> relationship.
>
> -Jon
>
Thank you for your insights Jon,
The duplicate data among the unique Primary Key'd rows of data could be
stored in a separate table (many to 1 relationship), and if I did that,
the problem would be solved.  This can be done and is a very good
solution, except that it would take a very long time to re-code the
already existing set of SQL commands and result sets inside the client
application.  I am hoping to avoid that.  If I can find a way to use
what has already been created without having to go back and re-code,
that would be my preference. Thus the hope for a database solution, if
possible.

Derrick


Re: Multiple row update with trigger

From
Derrick Betts
Date:
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;