From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Forums @ Existanze <forums@existanze.com>
To: pgsql-sql@postgresql.org
Subject: Re: Fwd: Start up question about triggers
Date: Fri, 23 Jun 2006 07:27:14 -0700 (PDT)
Message-ID: <20060623142714.99808.qmail@web31813.mail.mud.yahoo.com> (raw)
In-Reply-To: <200606230715.k5N7FHjO007409@auth-smtp.hol.gr>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, forums@existanze.com
Subject: Re: Fwd: Start up question about triggers
In-Reply-To: <20060623142714.99808.qmail@web31813.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox