Re: Fwd: Start up question about triggers - Mailing list pgsql-sql

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@postgresql.org 
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Forums @ 
> Existanze
> Sent: 23 June 2006 18:49
> To: pgsql-sql@postgresql.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@postgresql.org
> > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of George Weaver
> > Sent: 23 June 2006 15:38
> > To: Forums @ Existanze
> > Cc: pgsql-sql@postgresql.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@existanze.com>
> > To: <pgsql-sql@postgresql.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@postgresql.org 
> > >> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Richard 
> > >> Broersma Jr
> > >> Sent: 23 June 2006 08:10
> > >> To: Aaron Bono; pgsql-sql@postgresql.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@postgresql.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@postgresql.org 
> so that your
>        message can get through to the mailing list cleanly
>