Need help on triggers - postgres 9.1.2

Started by Khangelani Gamaalmost 12 years ago13 messagesgeneral

kgama@argility.com

almost 12 years ago

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();

Thanks

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.

Adrian Klaver

adrian.klaver@aklaver.com

almost 12 years ago

In reply to: Khangelani Gama (#1)

Re: 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Khangelani Gama

kgama@argility.com

almost 12 years ago

In reply to: Adrian Klaver (#2)

Re: 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Khangelani Gama

kgama@argility.com

almost 12 years ago

In reply to: Khangelani Gama (#3)

Re: 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.

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Khangelani Gama

kgama@argility.com

almost 12 years ago

In reply to: Khangelani Gama (#4)

Re: Need help on triggers - postgres 9.1.2

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

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

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

Thank you very much, I will have a look.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Khangelani Gama

kgama@argility.com

almost 12 years ago

In reply to: Khangelani Gama (#1)

Re: Need help on triggers - postgres 9.1.2

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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Import Notes

Reply to msg id not found: 20140522123854.GB6855@crankycanuck.ca

Raymond O'Donnell

rod@iol.ie

almost 12 years ago

In reply to: Khangelani Gama (#6)

Re: Need help on triggers - postgres 9.1.2

On 22/05/2014 13:48, Khangelani Gama wrote:

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

You need to specify where c_cde is coming from - so OLD.c_cde or
NEW.c_cde (or else SELECT .... FROM... if it's coming from somewhere else).

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Khangelani Gama

kgama@argility.com

almost 12 years ago

In reply to: Raymond O'Donnell (#7)

Re: Need help on triggers - postgres 9.1.2

Thanks , I will try that

-----Original Message-----
From: Raymond O'Donnell [mailto:rod@iol.ie]
Sent: Thursday, May 22, 2014 2:54 PM
To: Khangelani Gama; Andrew Sullivan; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2

On 22/05/2014 13:48, Khangelani Gama wrote:

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

You need to specify where c_cde is coming from - so OLD.c_cde or NEW.c_cde
(or else SELECT .... FROM... if it's coming from somewhere else).

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Igor Neyman

ineyman@perceptron.com

almost 12 years ago

In reply to: Khangelani Gama (#6)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#10

Karsten Hilbert

Karsten.Hilbert@gmx.net

almost 12 years ago

In reply to: Khangelani Gama (#6)

Re: Need help on triggers - postgres 9.1.2

On Thu, May 22, 2014 at 02:48:48PM +0200, Khangelani Gama wrote:

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();

PostgreSQL probably refuses to take wild guesses where
"c_cde" might come from. You need to tell it.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#11

Khangelani Gama

kgama@argility.com

almost 12 years ago

In reply to: Igor Neyman (#9)

Re: 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#12

Adrian Klaver

adrian.klaver@aklaver.com

almost 12 years ago

In reply to: Khangelani Gama (#11)

Re: Need help on triggers - postgres 9.1.2

On 05/22/2014 06:01 AM, Khangelani Gama wrote:

Many Thanks, I will try it now

In the link I sent previously:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html

there is a handy section (40.9.1. Triggers on data changes) that
explains what information is available to a plpgsql trigger and how to
access it. Might help to take out some of the mystery of what is going on.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#13

Khangelani Gama

kgama@argility.com

almost 12 years ago

In reply to: Khangelani Gama (#11)

Re: Need help on triggers - postgres 9.1.2

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: 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general