Re: need hint for a trigger...

fabrizio(dot)ermini(at)sysdat(dot)it wrote:
> On 16 Mar 2001, at 9:51, Jan Wieck wrote:
>
> > fabrizio(dot)ermini(at)sysdat(dot)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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com