Postgres Pro
Downloads
Home   >   mailing lists

Thread: The efficient method to create trigger

The efficient method to create trigger

From
Jignesh Shah
Date:
Hi,
 
I am new to PostgreSQL. I learnt most of things from postgresql.org site. I want to write a trigger for on my table tech_projects such that whenever INSERT, UPDATE or DELETE happen on this table, the trigger should get called and the updated/deleted row must be added to tech_projects_backup table. Moreover, if there are more than one row updated then I don't want to reiterate the client-server communication for each row but instead it should process all rows at once.
 
Could anyone please tell me which is the best way (PL/pgSQL, PL/Tcl, PL/Perl or PL/Python). I know the Perl but not aware of Tcl and Python. It would be great if some one could give example on processing multiple rows at once. I am not sure how could I use spi_prepare for this.
 
Thanks, Jignesh

Re: The efficient method to create trigger

From
"A. Kretschmer"
Date:
In response to Jignesh Shah :
> Hi,
>  
> I am new to PostgreSQL. I learnt most of things from postgresql.org site. I
> want to write a trigger for on my table tech_projects such that whenever
> INSERT, UPDATE or DELETE happen on this table, the trigger should get called
> and the updated/deleted row must be added to tech_projects_backup table.
> Moreover, if there are more than one row updated then I don't want to reiterate
> the client-server communication for each row but instead it should process all
> rows at once.

12:32 < akretschmer> ??tablelog
12:32 < rtfm_please> For information about tablelog
12:32 < rtfm_please> see
http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
12:32 < rtfm_please> or http://pgfoundry.org/projects/tablelog/

Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: The efficient method to create trigger

From
Jignesh Shah
Date:
Thanks but I already read about everything about PostgrSQL and don't want to read LogTable. Could any one please give me or point me some of Perl or C trigger examples that uses spi_prepare.
 
Thanks a lot,
Jignesh

On Tue, Aug 18, 2009 at 4:09 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
In response to Jignesh Shah :
> Hi,
>  
> I am new to PostgreSQL. I learnt most of things from postgresql.org site. I
> want to write a trigger for on my table tech_projects such that whenever
> INSERT, UPDATE or DELETE happen on this table, the trigger should get called
> and the updated/deleted row must be added to tech_projects_backup table.
> Moreover, if there are more than one row updated then I don't want to reiterate
> the client-server communication for each row but instead it should process all
> rows at once.

12:32 < akretschmer> ??tablelog
12:32 < rtfm_please> For information about tablelog
12:32 < rtfm_please> see http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
12:32 < rtfm_please> or http://pgfoundry.org/projects/tablelog/

Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: The efficient method to create trigger

From
Sean Davis
Date:


On Wed, Aug 19, 2009 at 1:42 AM, Jignesh Shah <jignesh.shah1980@gmail.com> wrote:
Thanks but I already read about everything about PostgrSQL and don't want to read LogTable. Could any one please give me or point me some of Perl or C trigger examples that uses spi_prepare.
 
Thanks a lot,
Jignesh

On Tue, Aug 18, 2009 at 4:09 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
In response to Jignesh Shah :
> Hi,
>  
> I am new to PostgreSQL. I learnt most of things from postgresql.org site. I
> want to write a trigger for on my table tech_projects such that whenever
> INSERT, UPDATE or DELETE happen on this table, the trigger should get called
> and the updated/deleted row must be added to tech_projects_backup table.
> Moreover, if there are more than one row updated then I don't want to reiterate
> the client-server communication for each row but instead it should process all
> rows at once.

Triggers operate on the server only.  There is no client-server communication for executing a trigger at the row level or at the statement level.

Sean