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

Hi

My problem is on two lines, INSERT statements, I get an error saying c_cde
does not exist

INSERT INTO center_changed SELECT c_cde, now();   - My problem is here: I
get an error saying c_cde does not exist, I cant how I can give
center_change table the results. Center table has got many rows so I need
to only get c_cde for only the row that was updated
         RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
       INSERT INTO center_changed SELECT c_cde, now();  My problem is
here: I get an error saying c_cde does not exist, I cant how I can give
center_change table the results. Center table has got many rows so I need
to only get c_cde for only the row that was inserted.




-----Original Message-----
From: Andrew Sullivan [mailto:ajs@crankycanuck.ca]
Sent: Thursday, May 22, 2014 2:39 PM
To: Khangelani Gama
Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2

[off list]

I don't know about others, but I don't see what your problem is.  Is the
problem that you are getting the new value in the center_changed table?
That's because you're doing an AFTER trigger ON UPDATE.  So you have the
new values.  Look at the special values NEW and OLD in the bit of the
manual about trigget functions?

A

On Thu, May 22, 2014 at 02:07:41PM +0200, Khangelani Gama wrote:
> Please help
>
> -----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();
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Khangelani Gama [mailto:kgama@argility.com]
> Sent: Wednesday, May 21, 2014 9:34 PM
> To: 'Adrian Klaver'; 'pgsql-general@postgresql.org'
> Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2
>
> Thank you very much, I will have a look.
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Wednesday, May 21, 2014 3:20 PM
> To: Khangelani Gama; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2
>
> On 05/21/2014 02:54 AM, Khangelani Gama wrote:
> > Hi
> >
> > I have a postgres 9 database, inside this database I need to create
> > a new table called *center_changed* that gets inserted by any change
> > that take place in a table called *center*. So I need to create
> > trigger to do this.
> >
> > *Example: *
> >
> > Table name*: center*(c_cde, c_dsc, ops_cde, grp_cde);
> >
> > Now on the table called *center , *I need to create an INSERT and
> > UPDATE trigger will insert the *c_cde * of the inserted or updated
> > *center* into the *center_changed* table
> >
> > Please help me
> >
> > I have this syntax below, but please help me with the overall query.
> >
> > CREATE TRIGGER check_center
> >
> >                 BEFORE INSERT OR UPDATE
> >
> >                 ON *cente*r FOR EACH ROW
> >
> >                 EXECUTE PROCEDURE check_center_changes();
>
> See here:
>
> http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html
>
> Example 40-4. A PL/pgSQL Trigger Procedure For Auditing
>
> >
> > Thanks
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
> 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.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Andrew Sullivan
ajs@crankycanuck.ca


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.