Обсуждение: Multiple row update with trigger
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
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°
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/
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
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;