DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=52Uj0LHgXjo/02j0WejdfRi9gGvCtsCu8n8IN00tzPSJTT8cAMBdlURQ3w9q++UPyT6qbkqiDtQFExN76LoOUDiYmoyN2jvSFDrK9G/oYRxAfQZVNROBtLteb4/jZWrVd3jE1roC7+weeBnykN6H6PSWvryO84pRix9d2CegugY=
	;
Message-ID: <20060623142714.99808.qmail@web31813.mail.mud.yahoo.com>
Date: Fri, 23 Jun 2006 07:27:14 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Fwd: Start up question about triggers
To: "Forums @ Existanze" <forums@existanze.com>, pgsql-sql@postgresql.org
In-Reply-To: <200606230715.k5N7FHjO007409@auth-smtp.hol.gr>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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