Re: Trigger problem - Mailing list pgsql-novice

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@esc1.com>
To: <pgsql-novice@postgresql.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@postgresql.org so that your
>       message can get through to the mailing list cleanly
>