Re: Trigger problem

Hi, tks for answering. What you told is true, if I use the query "update
test set registration_state='registered'" all the rows will be affected.
That is the problem, I need to know only the rows that are different. So,
for that, I was thinking about using the trigger for each row. So according
to what you said, I could use a table to save the cases where the
Old.registration_state != New.registration_state. But that gives me another
problem, if multiple process access the same table that would be a problem.
Can you help me. tks

----- Original Message -----
From: "Terry Lee Tucker" <terry(at)esc1(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, March 20, 2006 10:35 AM
Subject: Re: [NOVICE] Trigger problem

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>