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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)