Thread: Need help with a trigger
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';
create trigger transactions_trig after update on transactions
for each row execute procedure ComputeInvoiceTotal
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).
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).
Thanks
--
-------------------------------------------------------------------------
Medi Montaseri medi@CyberShell.com
Unix Distributed Systems Engineer HTTP://www.CyberShell.com
CyberShell Engineering
-------------------------------------------------------------------------
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.
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)
On Tue, 26 Feb 2002, Mourad EL HADJ MIMOUNE wrote: > 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? What's your function, and what version are you using?