Re: [GENERAL] Trigger question: ROW or STATEMENT?
that answered my question. Thanks everyone Patrick Hatcher Development Manager Analytics/MIO Macys.com Michael Fuhr [EMAIL PROTECTED] To 01/25/06 07:52 PM Patrick Hatcher [EMAIL PROTECTED] cc Doug McNaught [EMAIL PROTECTED], 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger question: ROW or STATEMENT?
Patrick Hatcher [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Trigger question: ROW or STATEMENT?
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
[EMAIL PROTECTED]
g To
Patrick Hatcher
01/25/06 11:45 AM [EMAIL PROTECTED]
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] Trigger question:
ROW or STATEMENT?
Patrick Hatcher [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Trigger question: ROW or STATEMENT?
Patrick Hatcher [EMAIL PROTECTED] 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
Re: [GENERAL] Trigger question: ROW or STATEMENT?
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 [EMAIL PROTECTED] g To Patrick Hatcher 01/25/06 01:36 PM [EMAIL PROTECTED] cc pgsql-general@postgresql.org Subject Re: [GENERAL] Trigger question: ROW or STATEMENT? Patrick Hatcher [EMAIL PROTECTED] 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
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
