Re: Need help with a trigger

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(at)megazone23(dot)bigpanda(dot)com>
To: "Medi Montaseri" <medi(at)cybershell(dot)com>
Cc: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)