> > -----Original Message----- > > From: Khangelani Gama [mailto:kgama@xxxxxxxxxxxx] > > Sent: Thursday, May 22, 2014 9:38 AM > > To: 'Adrian Klaver'; 'pgsql-general@xxxxxxxxxxxxxx' > > 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
