Re: 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