Re: Need help with a trigger - Mailing list pgsql-general

Hi,
I have a question in the same way. When I write OLD.oid in a trigger
procedure the trigger prompts "There are not Oid attribute for Old" Is this
a bug?
Mourad.
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Medi Montaseri" <medi@cybershell.com>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, February 05, 2002 7:49 AM
Subject: Re: [GENERAL] Need help with a trigger


> On Mon, 4 Feb 2002, Medi Montaseri wrote:
>
> > HI,
> >
> > Can someone help me with a trigger.....
> >
> > Given table invoices with ID, and Total (and bunch of other stuff) and
> > given
> > table Transactions with ID, InvoiceID, UnitCost, and Units where an
> > Invoice
> > consist of one or many Transactions. I want to write a trigger that if
> > UnitCost or
> > Units change, then visit all relevant Transactions and compute the new
> > Invoices.Total
> >
> > So I figured I need
> >
> > create function ComputeInvoiceTotal()
> > returns OPAQUE as '
> > begin
> >     ....here is where I don't know what to write...
> > end;'
> > language 'plpgsql';
>
> maybe something like:
> if (TG_OP = ''UPDATE'') then
>  update invoices
>  set total=total+NEW.UnitCost*NEW.Units-OLD.UnitCost*OLD.Units
>  where id=NEW.id;
>  return NEW;
> elsif (TG_OP = ''DELETE'') then
>  update invoices
>  set total=total-OLD.UnitCost*OLD.Units
>  return OLD;
> else
>  update invoices
>  set total=total+NEW.UnitCost*NEW.Units
>  where id=NEW.id;
>  return NEW;
> endif
>
> which doesn't actually recalculate from scratch, or you could do something
> similar with a set total=<subselect> that does recalculate for the id.
>
> > create trigger transactions_trig after update on transactions
> > for each row execute procedure ComputeInvoiceTotal
>
> And do it on updates and inserts and deletes probably.
>
> > I am a bit confused about parameter passing. Trigger Functions are
> > supposed to
> > take no arguments. that means somehow the body of the function will have
> > access
> > to the data. That would be NEW, and OLD special vars (I hope).
>
> Yep, and any arguments given on the create trigger line are passed in via
> TG_NARGS and TG_ARGV.
>
> > And I'm also confused about "for each row". What does it mean/do. I hope
> > its not
> > going to visit every row of a given table.  Because the initial
> > update/insert has
> > identified which row(s).
>
> For Each Row means for each row affected by the action, so if the update
> changes two rows the function will be called twice, once for each affected
> row (with OLD and NEW set appropriately).  This means if you do the full
> recalculation it might recalculate more than once for a particular invoice
> if two transactions were changed for it.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)