Re: Start up question about triggers

Chander Ganesan <chander@xxxxxxxxxx> · Tue, 20 Jun 2006 16:32:23 -0400

  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