[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    [postgis-users] Fwd: Trigger Function Only for Updated Row.
From:       Vera Green <vera.green.ca () gmail ! com>
Date:       2022-04-04 15:28:13
Message-ID: CAA_iUb-WA9pALE_5p4Ottr33qOWWTefHyaL5PWzzJpP-7dnX3A () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]


Hello,

Currently my trigger executes on ALL the rows in the table. I would like it
to execute ONLY on the UPDATED row. My thinking is that it is
inefficient to check all rows in a large table every time a single field is
updated. My main reason for wanting to do this is for efficient processing.

Here is my current code.

CREATE OR REPLACE FUNCTION public.pmap_tdl_loads_trucks()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
NEW.truck =
CASE
    WHEN (NEW.load_vol < 15) THEN '90 Barrel Truck'
    WHEN (NEW.load_vol < 16) THEN '100 Barrel Truck'
    WHEN (NEW.load_vol < 18) THEN '110 Barrel Truck'
else 'Unknown Truck'
END;
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER update_truck
    BEFORE INSERT OR UPDATE
    ON public.tdl_water_loads
    FOR EACH ROW
    EXECUTE FUNCTION public.pmap_tdl_loads_trucks();

Thank you,
Vera

[Attachment #5 (text/html)]

<div dir="ltr"><div class="gmail_quote"><div \
dir="ltr">Hello,<div><br></div><div>Currently my trigger executes on ALL the rows in \
the table. I would like it to execute ONLY on the UPDATED row. My thinking is that it \
is inefficient  to check all rows in a large table every time a single field is \
updated. My main reason for wanting to do this is for efficient  \
processing.</div><div><br></div><div>Here is my current code.  \
</div><div><br></div><div><font face="monospace">CREATE OR REPLACE FUNCTION \
public.pmap_tdl_loads_trucks()<br>      RETURNS trigger<br>      LANGUAGE \
&#39;plpgsql&#39;<br>      COST 100<br>      VOLATILE NOT LEAKPROOF<br>AS \
$BODY$<br>BEGIN<br>NEW.truck = <br>	CASE<br>      	WHEN (NEW.load_vol &lt; 15) THEN \
&#39;90 Barrel Truck&#39;<br>      WHEN (NEW.load_vol &lt; 16) THEN &#39;100 Barrel \
Truck&#39;<br>      WHEN (NEW.load_vol &lt; 18) THEN &#39;110 Barrel \
Truck&#39;<br>		else &#39;Unknown Truck&#39;<br>	END;<br>      RETURN \
NEW;	<br>END;<br>$BODY$;<br><br>CREATE TRIGGER update_truck<br>      BEFORE INSERT OR \
UPDATE <br>      ON  </font><span style="font-family:monospace">public.</span><span \
style="font-family:monospace">tdl_water_loads</span></div><div><font \
face="monospace">      FOR EACH ROW<br>      EXECUTE FUNCTION \
public.pmap_tdl_loads_trucks();   </font>  <br></div><div><br></div><div>Thank \
you,</div><div>Vera</div></div> </div></div>



_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]