Re: Stored Procedure / Trigger Strangeness

laotse(at)lumberjack(dot)snurgle(dot)org writes:
> ------------------------------------------------------------------ CREATE
> TRIGGER fti_employee_lastname AFTER UPDATE OR INSERT OR DELETE ON person
> FOR EACH ROW EXECUTE PROCEDURE fti(fti, lastname);

> CREATE TRIGGER fti_employee_firstname AFTER UPDATE OR INSERT OR DELETE ON
> person FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname);

> CREATE TRIGGER fti_employee_screenname AFTER UPDATE OR INSERT OR DELETE ON
> person FOR EACH ROW EXECUTE PROCEDURE fti(fti, screenname);

This will not work because there's no guarantee about the order of the
execution of the triggers. I haven't worked with fti much, but it's
obvious that it expects you to have only *one* trigger relating a given
indextable to the master --- on update, the trigger deletes all existing
indextable rows for that master row.

It looks like the intended way to index multiple columns using a single
indextable is

CREATE TRIGGER fti_person AFTER UPDATE OR INSERT OR DELETE ON person
FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname, lastname, screenname);

Or you could use a separate indextable for each column, but that might
not be what you want.

regards, tom lane