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

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