Re: create function and trigger to update column on table update

On Wed, Dec 15, 2010 at 5:02 PM, Dara Olson <dolson@glifwc.org> wrote:
> I am new to creating triggers/functions.  I am trying to create a trigger
> and function that when a specific table is updated or records added that it
> updates an existing column (catalog_number) from an existing column and
> latitude/longitude.  Below is what I have so far.  Does anyone know what I
> am doing wrong or if I am going in the complete wrong direction?  Is there a
> way to define that the column only gets updated on the records that were
> updated or inserted. Any help would be greatly appreciated!
> Thanks!
> Dara
>
> CREATE OR REPLACE FUNCTION invasive_species.update_catalog_number() RETURNS
> TRIGGER AS
> 'BEGIN
>
> IF TG_OP = "UPDATE" THEN
> UPDATE invasive_species.invspp_occurrence_data
> SET catalog_number = "tsn_char" || $_$ || x(centroid(transform(the_geom,
> 4326)))|| $_$ || y(centroid(transform(the_geom, 4326)));
>
> RETURN NEW;
> END IF;
> RETURN NULL;
> END;'
> LANGUAGE plpgsql;
>
> CREATE TRIGGER catalog_num_trigger AFTER UPDATE ON
> invasive_species.invspp_occurrence_data
> FOR EACH ROW EXECUTE PROCEDURE update_catalog_number();

It looks like the UPDATE statement within update_catalog_number() is
updating the entire invspp_occurrence_data table every time it gets
called -- that is, for every row which gets udpated in this table, a
full-table UPDATE is initiated by this trigger function. Is this
really what you need?

I didn't entirely understand your goals, but a much more common use of
trigger functions is roughly like this:
 * trigger is declared as BEFORE UPDATE instead of AFTER UPDATE as you have
 * trigger modifies column(s) of the row being updated by modifing the
NEW variable
 * trigger has RETURN NEW; at the end. You have RETURN NEW; as well as
RETURN NULL;, but because your trigger is an AFTER UPDATE trigger,
neither of these do anything useful.

See also Example 39-3 at:
http://developer.postgresql.org/pgdocs/postgres/plpgsql-trigger.html
for an example of a BEFORE INSERT OR UPDATE trigger.

Hope this helps..
Josh