Re: Fwd: Start up question about triggers

Hello again,

First of all thank you all for your effort in helping me solve this problem.

George's link seems like a complete auditing framework for a database, so I
will look into that, cuase it gives you a complete view of what is going on,
and I can have undo opertaions :-)

I have to say that this was not what I was looking for, but you guys helped
realized that it is the way to go.

Richard-
Your suggestion also makes a lot of sense and thank you for your suggestion.

What I have to point out is that no matter which solution we choose, we are
going to need to have an auditing table that represents each of our tables,
which as I mentioned are around 80 at the moment :-( so that is a lot of
extra work, but what can you do!

Image this scenario, and tell me if it wouldn't be awsome!

I have "n" number of tables each of which has different number of
columns,keys constraints and so on, and I have just ONE table with three
columns:

Logger
------
Log_id SERIAL PRIMARY KEY,
User VARCHAR(100) NOT NULL,
Query text

Then there exist a TG_QUERY parameter that we could use to get the actual
query ran by a user, so if I ran the imaginary query

INSERT INTO blah VALUES(DEFAULT,one,"23-08-3000")

I could use TG_QUERY and do

//trigger code
.........
INSERT INTO Logger VALUES(DEFAULT,user,TG_QUERY)
.....// end of trigger

This way I would end up with a log of the query ran on any of the tables so
theoretically

SELECT * FROM Logger ORDER BY Logger_id

Would return

Logger_id User Query
--------- ---- -----
2 fotis 'INSERT INTO blah VALUES(DEFAULT,one,"23-08-3000")'
4 fotis 'UPDATE seconblah SET parispo='hello' WHERE
parispo_id=50'
9 fotis 'DELETE FROM blah WHERE id=30'
12 fotis 'INSERT INTO seconblah VALUES(DEFAULT,'another')'
And so on....

This way I would have all the necessary queries to restore an incomplete
database with the appropiate data from some point in time to another in the
future.

The only difference between the theoretical method and the ones already
suggested is that I will still need "n" trigger function for each of the "n"
tables, but only ONE auditing table.

Would it be much nicer :-)

Once again thank you very much for all your help,
Fotis

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of George Weaver
> Sent: 23 June 2006 15:38
> To: Forums @ Existanze
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
> Hi Fotis,
>
> If you end up having to create a solution for each of the 80
> tables, you may want to check out the following (may also
> give you addtional ideas for what you're trying to achieve):
>
> http://www.varlena.com/GeneralBits/104.php (Logging Audit
> Changes with Composite Typed Columns).
>
> Regards,
> George
>
>
> ----- Original Message -----
> From: "Forums @ Existanze" <forums(at)existanze(dot)com>
> To: <pgsql-sql(at)postgresql(dot)org>
> Sent: Friday, June 23, 2006 2:15 AM
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
>
> >
> > Hello again aaron,
> >
> > Im really interested in the part where you say "generic
> trigger" can you
> > give me some tips? As to how I will go about that? I had
> already read the
> > links that Richard gave, I new I could get the values like
> that. So right
> > now I will have to create a trigger for each of my tables
> to create the
> > necessary queries, or I could do it "generically" :-)
> >
> > Best Regards,
> > Fotis
> >
> >> -----Original Message-----
> >> From: pgsql-sql-owner(at)postgresql(dot)org
> >> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Richard
> >> Broersma Jr
> >> Sent: 23 June 2006 08:10
> >> To: Aaron Bono; pgsql-sql(at)postgresql(dot)org
> >> Subject: Re: Fwd: [SQL] Start up question about triggers
> >>
> >> > I did some research and can't even find a way to get meta
> >> data in a trigger.
> >> >
> >> > In a trigger, is there a way to inspect OLD and NEW to see what
> >> > columns are there and see what has changed? If so, you
> may not be
> >> > able to grab the actual query but you could create a
> >> generic trigger
> >> > that reconstructs a possible update/insert/delete for any
> >> table in your database.
> >> >
> >> > Does anyone know of a good place to go get information
> about using
> >> > meta data in a stored procedure or trigger?
> >>
> >> yes.
> >> See the section "User Comments" at the very bottom of Chapter
> >> 33 after "Writing Trigger Functions in C". It is odd that a
> >> PL_PGSQL example is given at the end a chapter for triggers
> >> written in C.
> >>
> >> http://www.postgresql.org/docs/8.1/interactive/trigger-example.html
> >>
> >> Also see the entire chapter 36.10 "Trigger Procedures"
> >> http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html
> >>
> >> Hope this is what you are looking for.
> >>
> >> Regards,
> >>
> >> Richard Broersma Jr.
> >>
> >> ---------------------------(end of
> >> broadcast)---------------------------
> >> TIP 9: In versions below 8.0, the planner will ignore your
> desire to
> >> choose an index scan if your joining column's
> datatypes do not
> >> match
> >>
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> so that your
> message can get through to the mailing list cleanly
>