From: | "Luis Silva" <lfs12(at)hotmail(dot)com> |
---|---|
To: | "Terry Lee Tucker" <terry(at)esc1(dot)com>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Trigger problem |
Date: | 2006-03-21 15:26:38 |
Message-ID: | BAY115-DAV4C0495DFCCC9851E24A2CB5D80@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Lane Van Ingen | 2006-03-21 21:49:37 | Question about PostgreSQL Metadata |
Previous Message | Terry Lee Tucker | 2006-03-20 10:35:18 | Re: Trigger problem |