PostgreSQL Discussion - Trigger question: ROW or STATEMENT?

Author

Trigger question: ROW or STATEMENT?

Patrick Hatcher

2006-01-26, 4:57 pm

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Doug McNaught

2006-01-26, 4:57 pm

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Patrick Hatcher

2006-01-26, 4:57 pm

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_trani
tem_sub();

CREATE OR REPLACE FUNCTION cdm. insert_cost_to_trani
tem_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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Doug McNaught

2006-01-26, 4:57 pm

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Patrick Hatcher

2006-01-26, 4:57 pm

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Michael Fuhr

2006-01-26, 4:57 pm

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/...GGER-DEFINITION

The documentation mentions that if you have no specific reason to
use before or after, then before is more efficient.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Patrick Hatcher

2006-01-26, 4:57 pm

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/...GGER-DEFINITION

The documentation mentions that if you have no specific reason to
use before or after, then before is more efficient.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links