> 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
