Thread: need hint for a trigger...
Hi all. I have to write a trigger and I have never did it before, so if there is someone who could give me a start, I will be very grateful... I have two tables that I want to keep "partially" synced, i.e.: table1 (field1,field2,field3) table2 (field1,field2,field3, ... some other fields). I've created them using the same data for the common fields, and then populated the other fields of table2. field1 is unique key for both tables. I would like that when a record gets changed in table1, the same changes reflect on the correspondent record in table2. Eventual changes made on the first 3 fields of table2 should be overwritten, leaving the other fields untouched. I was presuming this could be done with a trigger on UPDATE on table1, but I don't know how to write it... I know the first reply that I can expect is RTFM, but if a gentle soul has the time to write an example... TIA, Ciao /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 faermini@tin.it loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) faermini@sms.tin.it
From: <fabrizio.ermini@sysdat.it> > Hi all. I have to write a trigger and I have never did it before, so if > there is someone who could give me a start, I will be very grateful... > > I have two tables that I want to keep "partially" synced, i.e.: [snip] > I would like that when a record gets changed in table1, the same > changes reflect on the correspondent record in table2. Eventual > changes made on the first 3 fields of table2 should be overwritten, > leaving the other fields untouched. > > I was presuming this could be done with a trigger on UPDATE on > table1, but I don't know how to write it... I know the first reply that I > can expect is RTFM, but if a gentle soul has the time to write an > example... You presume right. A rule might prove more efficient (can't tell from your example), but a trigger will work just fine. Check the developer's docs at http://www.postgresql.org/devel-corner/docs/programmer/ Also try http://techdocs.postgresql.org and look in PostgeSQL Notes for the Automating Processes chapter - there are examples there (logging changes is very similar to what you want). Also see http://www.brasileiro.net/postgres for the brand new plpgsql cookbook which has several examples already and should be growing all the time. Try adapting one of the examples you find, and if you have problems post the code and table definitions back to the list. One tip I can give is to build the function and trigger in a text-editor then execute it with psql -f <file> - makes debugging much easier. If you have any comments on any of the documentation, please let the relevant author know, most of this stuff is brand new. - Richard Huxton
fabrizio.ermini@sysdat.it wrote:
> Hi all. I have to write a trigger and I have never did it before, so if
> there is someone who could give me a start, I will be very grateful...
>
> I have two tables that I want to keep "partially" synced, i.e.:
>
> table1 (field1,field2,field3)
> table2 (field1,field2,field3, ... some other fields).
>
> I've created them using the same data for the common fields, and
> then populated the other fields of table2. field1 is unique key for
> both tables.
>
> I would like that when a record gets changed in table1, the same
> changes reflect on the correspondent record in table2. Eventual
> changes made on the first 3 fields of table2 should be overwritten,
> leaving the other fields untouched.
Since field1 is the key, it seems impossible to identify the
row if someone changed field1 in table2. As long as nobody
does that,
CREATE FUNCTION table1_upd () RETURNS opaque AS '
BEGIN
UPDATE table2 SET field1 = NEW.field1,
field2 = NEW.field2,
field3 = NEW.field3
WHERE field1 = OLD.field1;
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER table1_upd AFTER UPDATE ON table1
FOR EACH ROW EXECUTE PROCEDURE table1_upd ();
Jan
>
> I was presuming this could be done with a trigger on UPDATE on
> table1, but I don't know how to write it... I know the first reply that I
> can expect is RTFM, but if a gentle soul has the time to write an
> example...
>
> TIA,
> Ciao
>
>
> /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/
>
> Fabrizio Ermini Alternate E-mail:
> C.so Umberto, 7 faermini@tin.it
> loc. Meleto Valdarno Mail on GSM: (keep it short!)
> 52020 Cavriglia (AR) faermini@sms.tin.it
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On 16 Mar 2001, at 9:51, Jan Wieck wrote: > fabrizio.ermini@sysdat.it wrote: > > > > I would like that when a record gets changed in table1, the same > > changes reflect on the correspondent record in table2. Eventual > > changes made on the first 3 fields of table2 should be overwritten, > > leaving the other fields untouched. > > Since field1 is the key, it seems impossible to identify the > row if someone changed field1 in table2. As long as nobody > does that, > Right. I've taken measures at application level against this happening, indeed; However, I'll double check them... > CREATE FUNCTION table1_upd () RETURNS opaque AS ' > BEGIN > UPDATE table2 SET field1 = NEW.field1, > field2 = NEW.field2, > field3 = NEW.field3 > WHERE field1 = OLD.field1; > RETURN NEW; > END;' > LANGUAGE 'plpgsql'; > > CREATE TRIGGER table1_upd AFTER UPDATE ON table1 > FOR EACH ROW EXECUTE PROCEDURE table1_upd (); > > Jan > Now this is what I call a good example! Thank you very much Jan, you've saved me a lot of time. Ciao! /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 faermini@tin.it loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) faermini@sms.tin.it
fabrizio.ermini@sysdat.it wrote:
> On 16 Mar 2001, at 9:51, Jan Wieck wrote:
>
> > fabrizio.ermini@sysdat.it wrote:
> > >
> > > I would like that when a record gets changed in table1, the same
> > > changes reflect on the correspondent record in table2. Eventual
> > > changes made on the first 3 fields of table2 should be overwritten,
> > > leaving the other fields untouched.
> >
> > Since field1 is the key, it seems impossible to identify the
> > row if someone changed field1 in table2. As long as nobody
> > does that,
> >
> Right. I've taken measures at application level against this
> happening, indeed; However, I'll double check them...
You might be able to have them at the database level as well.
If you really have a 1:1 relationship between table1.field1
and table2.field1, so that whenever a key in table1 exists, a
corresponding row in table2 must and vice versa, look at
this:
CREATE TABLE table1 (
field1 integer PRIMARY KEY,
field2 ...
);
CREATE TABLE table2 (
field1 integer PRIMARY KEY,
field2 ...
FOREIGN KEY (field1) REFERENCES table1 (field1)
ON DELETE CASCADE
ON UPDATE CASCADE
INITIALLY DEFERRED
);
ALTER TABLE table1 ADD
FOREIGN KEY (field1) REFERENCES table2 (field1)
INITIALLY DEFERRED;
Let's go through step by step.
1. Table1 is created with the usual PRIMARY KEY on field1,
that guarantees that field1 will be unique.
2. Table2 is created, again field1 is guaranteed to be
unique, plus the DB is instructed to check on every
INSERT or UPDATE to a row if such a key exists in table1.
The default this foreign key constraint further would be,
that table1.field1 cannot be changed or the row deleted
as long as there is a row in table2 referencing it. The
ON DELETE CASCADE and ON UPDATE CASCADE told the database
to delete the referencing rows silently from table2
instead or update their field1 as well to let the
references follow.
The INITIALLY DEFERRED I'll explain later.
3. We add a similar foreign key constraint to table1, now
telling that for each key in field1 a corresponding key
in table2.field1 must exist. We omit the ON ... CASCADE
clauses, so that someone must delete from table1 and
cannot from table2.
What we got so far is that all values in table1.field1 must
be unique, all values in table2.field1 must be unique, that
each value in table1.field1 must have a matching row in
table2 and vice versa. Additionally, if we UPDATE or DELETE
table1, the changes "to field1" will automatically propagate
to table2. Nobody can change table2.field1 and nobody can
delete from table2, all this must be done via changes to
table1 and is automated then.
Well, now let's think how we get some data into it :-)
At the time we insert a row into table1, the corresponding
row in table2 cannot exist (up to now it's empty). So the
constraint must reject the INSERT. But the same happens for
table2, because we aren't able to put it into table1. This
is the point where the INITIALLY DEFERRED comes into play.
This instructs, that the check of the constraint will be
delayed until transaction commit. So the sequence
BEGIN;
INSERT INTO table1 ...
INSERT INTO table2 ...
COMMIT WORK;
will work, because the constraints will be checked at COMMIT
and by then all the required rows are in place.
For your actual application this'd *FORCE* the programmer (I
assume that'd be you) to use transactions to get data into,
and that he cannot insert one row without the other. Whenever
he tries to, he'll get an error and an implicit rollback from
the database.
>
> > CREATE FUNCTION table1_upd () RETURNS opaque AS '
> > BEGIN
> > UPDATE table2 SET field1 = NEW.field1,
> > field2 = NEW.field2,
> > field3 = NEW.field3
> > WHERE field1 = OLD.field1;
> > RETURN NEW;
> > END;'
> > LANGUAGE 'plpgsql';
> >
> > CREATE TRIGGER table1_upd AFTER UPDATE ON table1
> > FOR EACH ROW EXECUTE PROCEDURE table1_upd ();
> >
> > Jan
> >
> Now this is what I call a good example!
> Thank you very much Jan, you've saved me a lot of time.
We're all put onto this earth to accomplish a couple of
things. Actually, I'm that far behind that I might live
forever.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com