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
