Re: Need help on triggers - postgres 9.1.2 - Mailing list pgsql-general

Thank you very much to everyone, it worked

-----Original Message-----
From: Khangelani Gama [mailto:kgama@argility.com]
Sent: Thursday, May 22, 2014 3:01 PM
To: 'Igor Neyman'; 'Andrew Sullivan'; 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2

Many Thanks, I will try it now

-----Original Message-----
From: Igor Neyman [mailto:ineyman@perceptron.com]
Sent: Thursday, May 22, 2014 2:55 PM
To: Khangelani Gama; Andrew Sullivan; pgsql-general@postgresql.org
Subject: RE: [GENERAL] 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


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer
immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no
liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.