Skip site navigation (1) Skip section navigation (2)

Re: Trigger problem

From: Terry Lee Tucker <terry(at)esc1(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Trigger problem
Date: 2006-03-20 10:35:18
Message-ID: 200603200535.18218.terry@esc1.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Monday 20 March 2006 05:23 am, Luis Silva saith:
> Hi there, I'm having a problem, When I'm doing an update to one of the
> fields of my table I need to know if it's changed.
> For exemple I have the table with the columns
> id,identity,registration_state.
>
> Test:
> id	|	identity	|	registration_state
> 1	|	Joe		|	registered
> 2	|	Tom		|	not_registered
> 3	|	James		|	unregistered
>
> when i do "update test set registration_state='registered'" I need to know
> for which identities the registration_state was change (and then return
> it). In this case, Tom and James.

I believe that all three will be updated with the above statement.

>
> I was thinking about using a trigger function for the updates, using
> pl/pgsql (using before in the trigger). the problem is that I don't know
> how to get the old registration_state when I have multiple rows affected. I
> known that if I use OLD.registration_state it works for one rows.  Can you
> help for multiple? tks a lot in advance
>

Define the BEFORE trigger so that it fires for each row as in:
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
    ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE funcname ( arguments )

Pick the ROW option. This will cause the trigger to fire for each row updated 
in your statement. You are going to need another table called audit or 
something so that your trigger can insert information about the change into 
that table. 

>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


In response to

Responses

pgsql-novice by date

Next:From: Luis SilvaDate: 2006-03-21 15:26:38
Subject: Re: Trigger problem
Previous:From: Luis SilvaDate: 2006-03-20 10:23:51
Subject: Trigger problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group