Re: need hint for a trigger... - Mailing list pgsql-general

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