> 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.
