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

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