Search Postgresql Archives

Re: Start up question about triggers

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux