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