Re: Start up question about triggers

Thank you for your answer,

We had though about your solution, the problem is that we have around 80
tables at the moment so your method would suggest adding another 80.

I was wondering if it was possible to retrieve the query in the trigger
function, cause what we wanted to achieve was to trigger a query log when
any insert or update or delete operation was made on any of the 80 tables.
This way we would have something like a query log table. Which will have the
queries in the order that they were executed by n number of clients.

Say one client updates a row, and the next client deletes it, we want to
know the queries that occurred in that particular order.

I hope this makes some sense :-)

I should also mention that what we are trying to achieve is some sort of
partial backup operation. Because of bad initial design, we didn't foresee
this comming. So now we have two options, changing all the tables,queries
and code, to contain two timestamps columns representing created and updated
row, a flag for deleted row, and have some sort of maintanance operation
that will clean all the deleted records, and create insert/update statements
for those records that have been updated ie( time_of_update >
time_of_creation). This will give us a list of operation (INSERT OR UPDATE
statements) that can be written to a file, and run from a file.

So if I had 10 partiall backups and ran them sequencially I would in theory
have the data that I originally had. At the moment we are doing full back
using pgdump, but this is another type of requirement.


Any ideas greatly appreciated.

Best Regards,
Fotis

_____

From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Aaron Bono
Sent: 22 June 2006 19:03
To: Forums @ Existanze
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Start up question about triggers

Why not just create a history table and have the trigger copy the data out
of the table into the history table with a time stamp of the change. Then
you don't need the query.

For example
Table A
a_id,
a_value1,
a_value2

Table A_hist
a_id,
a_dt,
a_value1,
a_value2

Then A_hist has a PK of a_id, a_dt

This would also be a lot easier to see WHAT changed and WHEN. You can use
the NEW.col and OLD.col to see the new and old values during inserts and
updates.

Of course, I don't know your need so this may not be achieving your goal.

-Aaron Bono

On 6/22/06, Forums @ Existanze <forums(at)existanze(dot)com> wrote:

Sorry This is the complete message

Hello all,

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

_____

From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Forums @ Existanze
Sent: 22 June 2006 12:19
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] Start up question about triggers

Hello all,

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