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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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
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" <[EMAIL PROTECTED]> To: 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Fwd: [SQL] Start up question about triggers
> 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" :-) Sorry, I guess I haven't kept up to speed with this thread. However, from chapter 36.10 http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html Notice the variables that you have to work with in a trigger function: TG_WHEN Data type text; a string of either BEFORE or AFTER depending on the trigger's definition. TG_RELNAME = Data type name; the name of the table that caused the trigger invocation. TG_OP = Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired. NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers. Also, notice chapter 9.19 http://www.postgresql.org/docs/8.1/interactive/functions-info.html current_user = user name of current execution context So with this information couldn't one (from a trigger function) insert a record in to a history table with the following columns?: Then for each column of the affect table if old.tbl_col1 != new.tbl_col1 then add a record to the history as follows. TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col1 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col2 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col3 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_coln is this something like what you had in mind? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
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 UserQuery - - 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > To: > 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: [EMAIL PROTECTED] > >> [mailto:[EMAIL PROTECTED] 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 bel
Re: Fwd: [SQL] 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 UserQuery > - - > 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' > 12fotis '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: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > > To: > > 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 > >
Re: Fwd: [SQL] Start up question about triggers
On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote: > > 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 Which "actual query"? By the time the trigger fires, the query might already have been rewritten, I think. No? I _think_ that even BEFORE triggers happen after the rewriter stage is called, but someone who has more clue will be able to correct me if I'm wrong. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Fwd: [SQL] Start up question about triggers
Andrew Sullivan <[EMAIL PROTECTED]> writes: > On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote: >> 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 > Which "actual query"? By the time the trigger fires, the query might > already have been rewritten, I think. No? I _think_ that even > BEFORE triggers happen after the rewriter stage is called, but > someone who has more clue will be able to correct me if I'm wrong. Even if you could get hold of the user query text, it'd be a serious mistake to imagine that it tells you everything you need to know about the update. Aside from rule rewrites, previous BEFORE triggers could have changed fields that are mentioned nowhere in the query. The only safe way to determine what's going on is to compare the OLD and NEW row values. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Fwd: [SQL] Start up question about triggers
This is why I was searching for good meta data.Here is a thought. If your trigger has the OLD and NEW, is there a way to get a list of fields from OLD and NEW? If TG_RELNAME is the name of the table, could you just ask PostgreSQL what the columns are in that table, iterate through those columns, get the values for each of these columns out of OLD and NEW and save the old/new values? What I really cannot find is a way to _dynamically_ in the trigger ask what COLUMNS are in OLD and NEW. If we had:table affected (TG_RELNAME?)columns that are in the tableold values for each of these columns new values for each of these columnsThen you could store this information into two tables:modify_table modify_table_id modify_dt table_namemodify_value modify_value_id modify_table_id old_value new_valueI wish I had more experience with stored procedures - I know what I would try to do, just not if it is possible or how to implement it.Tom makes a very good point that having the actual query is not going to help in a general sense. If someone does an insert or update which fires a trigger that does further updates and inserts or even changes values on the fly, the inserts and updates you record will NOT reveal exactly what is going on. Keeping the values from OLD and NEW at the very end would be much more useful. -Aaron BonoOn 6/23/06, Tom Lane <[EMAIL PROTECTED]> wrote: Andrew Sullivan <[EMAIL PROTECTED]> writes:> On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote:>> 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> Which "actual query"? By the time the trigger fires, the query might> already have been rewritten, I think. No? I _think_ that even > BEFORE triggers happen after the rewriter stage is called, but> someone who has more clue will be able to correct me if I'm wrong.Even if you could get hold of the user query text, it'd be a serious mistake to imagine that it tells you everything you need to know aboutthe update. Aside from rule rewrites, previous BEFORE triggers couldhave changed fields that are mentioned nowhere in the query. The only safe way to determine what's going on is to compare the OLD and NEWrow values.regards, tom lane
