Re: Trigger question: ROW or STATEMENT?

[Patrick Hatcher <PHatcher@xxxxxxxxx>]

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@xxxxxxxxxxx                                             
             g>                                                         To 
                                       Patrick Hatcher                     
             01/25/06 11:45 AM         <PHatcher@xxxxxxxxx>                
                                                                        cc 
                                       pgsql-general@xxxxxxxxxxxxxx        
                                                                   Subject 
                                       Re: [GENERAL] Trigger question:     
                                       ROW or STATEMENT?                   
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




Patrick Hatcher <PHatcher@xxxxxxxxx> 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