Re: Need help on triggers - postgres 9.1.2

> > -----Original Message-----
> > From: Khangelani Gama [mailto:kgama@argility.com]
> > Sent: Thursday, May 22, 2014 9:38 AM
> > To: 'Adrian Klaver'; 'pgsql-general@postgresql.org'
> > Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2
> >
> > Hi all
> >
> > Something it's tricky for me here, see my trigger I wrote below. What
> > can I do to insert c_code from center table  INTO center_changed table
> > with ONLY the c_code where the update was made or where an INSERT of
> > the new entry INTO center table what made  .
> >
> >
> > Let's say the center table has got the following values. When I try to
> > change c_dsc from KITWE to KIT where c_code ='0204' the trigger should
> > take cde 0204 and insert it into center_changed table with a time
> > stamp. So the main problem I have it's to populate the table called
> center_changed.
> >
> >
> > c_cde |                    c_desc                     | c_active
> > --------+------------------------------------------------+----------
> >  0094   | GABORONE WAREHOUSE                             | f
> >  0204   | KITWE                                          | t
> >
> >
> >
> >
> >
> >
> > CREATE TABLE center_changed (
> >     c_cde            text   NOT NULL,
> >     stamp             timestamp NOT NULL
> > );
> >
> > CREATE OR REPLACE FUNCTION check_center_changes() RETURNS
> TRIGGER AS
> > $center_changed$
> >
> > BEGIN
> >
> >         IF (TG_OP = 'UPDATE') THEN
> >             INSERT INTO center_changed SELECT c_cde, now();
> >             RETURN NEW;
> >         ELSIF (TG_OP = 'INSERT') THEN
> >             INSERT INTO center_changed SELECT c_cde, now();
> >             RETURN NEW;
> >         END IF;
> >         RETURN NULL;
> > END;
> > $center_changed$ LANGUAGE plpgsql;
> >
> > CREATE TRIGGER center_changed
> > AFTER INSERT OR UPDATE ON center
> >         FOR EACH ROW EXECUTE PROCEDURE check_center_changes();
> >

This should work:

CREATE OR REPLACE FUNCTION check_center_changes() RETURNS
 TRIGGER AS  $center_changed$
 BEGIN
    INSERT INTO center_changed VALUES(new.c_cde, now());
             RETURN NEW;
END;
 $center_changed$ LANGUAGE plpgsql;

 CREATE TRIGGER center_changed
 AFTER INSERT OR UPDATE ON center
         FOR EACH ROW EXECUTE PROCEDURE check_center_changes();

Regards,
Igor Neyman