Re: Fwd: Start up question about triggers

Hello again,

Just a thought! Do any of you know if this is possible?

I have a table person

CREATE TABLE person(
person_id SERIAL PRIMARY KEY,
person_name VARCHAR(100) NOT NULL,
person_lastname VARCHAR(100) NOT NULL
);

And a table audit

CREATE TABLE audit(
audit_id SERIAL PRIMARY KEY,
audit_person person NOT NULL
);

As you can see in the audit table, "audit_person" is of type "person" which
is my second table.

I have managed to save the NEW object in a trigger fuction which represent a
person.

So issuing
INSERT INTO person VALUES(DEFAULT,'name','lastname');

WILL create a row for the audit table as such:

SELECT * FROM audit;

Returns
Id Person
-- ------
1 (1,name,lastname)

I then delete all from person and try to do this, I know it doesn't work but
is it possible?

DELETE FROM person;

INSERT INTO person SELECT (audit_person::person) FROM audit;

If the above was possible then I could theoretically just save the new
object in the audit table for all my tables!!

Any thoughts

Best Regards,
Fotis

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Forums @
> Existanze
> Sent: 23 June 2006 18:49
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Re: Fwd: [SQL] 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
> >
>
>
> ---------------------------(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
>