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