Thread: Trigger question: ROW or STATEMENT?
Attempting to do my first trigger and I'm confused about which FOR EACH I should use: ROW or STATEMENT. I import about 80K rows into an existing table each day. If I do a STATEMENT, will the changes only happen on the new 80K rows I inserted or will it be for all rows in the table - currently about 12M. TIA Patrick Hatcher
Patrick Hatcher <PHatcher@macys.com> writes: > Attempting to do my first trigger and I'm confused about which FOR EACH I > should use: ROW or STATEMENT. I import about 80K rows into an existing > table each day. If I do a STATEMENT, will the changes only happen on the > new 80K rows I inserted or will it be for all rows in the table - currently > about 12M. If you told us what you want the trigger to do it would probably be helpful. -Doug
Here is the trigger the way it is currently written. I add some additional
information from another table:
CREATE TRIGGER item_cost_trig
BEFORE INSERT
ON cdm.cdm_ddw_tran_item
FOR EACH ROW
EXECUTE PROCEDURE cdm.insert_cost_to_tranitem_sub();
CREATE OR REPLACE FUNCTION cdm.insert_cost_to_tranitem_sub()
RETURNS "trigger" AS
'DECLARE
varCost float8;
varOwned float8;
varDept int4;
varVend int4;
varMstyle int4;
BEGIN
IF NEW.appl_id IN (''MCOM'',''NET'') THEN
select into varCost, varOwned, varDept, varVend,varMstyle cost,owned,
dept, vend,mstyle
from public.flbasics where upc = NEW.item_upc limit 1;
IF FOUND THEN
NEW.cost :=varCost;
NEW.owned :=varOwned;
NEW.dept_id := varDept;
NEW.vend_id := varVend;
NEW.mkstyl := varMstyle;
ELSE
NEW.cost :=0;
NEW.owned :=0;
END IF;
ELSE
NEW.cost :=0;
NEW.owned :=0;
END IF;
RETURN NEW;
END;'
LANGUAGE 'plpgsql' VOLATILE;
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
415-422-1610
Doug McNaught
<doug@mcnaught.or
g> To
Patrick Hatcher
01/25/06 11:45 AM <PHatcher@macys.com>
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] Trigger question:
ROW or STATEMENT?
Patrick Hatcher <PHatcher@macys.com> writes:
> Attempting to do my first trigger and I'm confused about which FOR EACH I
> should use: ROW or STATEMENT. I import about 80K rows into an existing
> table each day. If I do a STATEMENT, will the changes only happen on the
> new 80K rows I inserted or will it be for all rows in the table -
currently
> about 12M.
If you told us what you want the trigger to do it would probably be
helpful.
-Doug
Patrick Hatcher <PHatcher@macys.com> writes: > Here is the trigger the way it is currently written. I add some additional > information from another table: If you're modifying each row before it goes in, it should definitely be a FOR EACH ROW trigger. -Doug
Would I gain any advantage by changing to it to fire after the insert?
thanks again for the help
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
415-422-1610
Doug McNaught
<doug@mcnaught.or
g> To
Patrick Hatcher
01/25/06 01:36 PM <PHatcher@macys.com>
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] Trigger question:
ROW or STATEMENT?
Patrick Hatcher <PHatcher@macys.com> writes:
> Here is the trigger the way it is currently written. I add some
additional
> information from another table:
If you're modifying each row before it goes in, it should definitely
be a FOR EACH ROW trigger.
-Doug
On Wed, Jan 25, 2006 at 02:47:45PM -0800, Patrick Hatcher wrote: > Would I gain any advantage by changing to it to fire after the insert? If you're modifying the row then the trigger must fire before the insert. An after trigger can abort the operation by raising an error and it can perform actions like updating another table, but by the time an after trigger fires it's too late to change the current row (except via an UPDATE, and then you must beware of cascading triggers leading to infinite recursion). You might want to read "Overview of Trigger Behavior" in the documentation -- it describes the various kinds of triggers (row/statement and before/after) and when certain types are appropriate: http://www.postgresql.org/docs/8.1/interactive/triggers.html#TRIGGER-DEFINITION The documentation mentions that if you have no specific reason to use before or after, then before is more efficient. -- Michael Fuhr
that answered my question.
Thanks everyone
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
Michael Fuhr
<mike@fuhr.org>
To
01/25/06 07:52 PM Patrick Hatcher
<PHatcher@macys.com>
cc
Doug McNaught <doug@mcnaught.org>,
pgsql-general@postgresql.org
Subject
Re: [GENERAL] Trigger question:
ROW or STATEMENT?
On Wed, Jan 25, 2006 at 02:47:45PM -0800, Patrick Hatcher wrote:
> Would I gain any advantage by changing to it to fire after the insert?
If you're modifying the row then the trigger must fire before the
insert. An after trigger can abort the operation by raising an
error and it can perform actions like updating another table, but
by the time an after trigger fires it's too late to change the
current row (except via an UPDATE, and then you must beware of
cascading triggers leading to infinite recursion).
You might want to read "Overview of Trigger Behavior" in the
documentation -- it describes the various kinds of triggers
(row/statement and before/after) and when certain types are
appropriate:
http://www.postgresql.org/docs/8.1/interactive/triggers.html#TRIGGER-DEFINITION
The documentation mentions that if you have no specific reason to
use before or after, then before is more efficient.
--
Michael Fuhr