Re: Need help on triggers - postgres 9.1.2

> > -----Original Message-----
> > From: Khangelani Gama [mailto:kgama(at)argility(dot)com]
> > Sent: Thursday, May 22, 2014 9:38 AM
> > To: 'Adrian Klaver'; 'pgsql-general(at)postgresql(dot)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