Re: Start up question about triggers

>
> I know that this question may be really simple, but I have decided to
> ask here due to fact that I don't know how to search for this on
> google or on the docs.
>
> I created a trigger fuction which updates a specific row in some table
> A. Is it possible to retain the query that was used to trigger the
> function. For example
>
> Table A
> query_row_id
> query_row
>
>
>
> TABLE B
> id
> name
>
>
>
>
>
> If I create a trigger on table B that says that after the insert
> command to write the query into table A. So if I do
>
> insert into B values(1,"Blah")
>
> this will trigger my trigger. Is there any way to get the "insert into
> B values(1,"Blah")? At the moment I can see only the type of query
> that it is (INSERT UPDATE DELETE)
>
> best regards,
> Fotis
>
Are you looking for the exact query that was entered? If so, I'm not
sure you can get the *exact* query (aside from turning on some logging
and looking at the logs ... but that wouldn't go in a table ;-) ).
However, you can get all of the values that were used - and essentially
get all of the information that was inserted/updated. Basically, you
can recreate a query that will have the exact same effect - though it
may not be the exact same query - assuming you have primary keys on all
of your tables.

Look at the 'NEW' and 'OLD' records within your trigger . In the case
of an INSERT, the 'NEW' record will contain all of the columns that are
being inserted (so you could simply store all of the values in the NEW
record to get the inserted information and store it). In the case of
UPDATE, the NEW record will contain all of the new row data, and the OLD
record will contain the old row data. In the case of DELETE, the OLD
record will contain the information pertaining to the record being deleted.

http://www.postgresql.org/docs/8.1/static/triggers.html

You might also take a look at Slony-I, since it does something similar
with INSERT UPDATE and DELETE triggers - it stores the information that
has changed so that the slon process can replicate it to other servers.

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com