I am trying to create a (set of) trigger(s) that update additional table(s) upon INSERT/UPDATE to the original table(s). Simple enough.
So I have a Trigger:
CREATE TRIGGER send_collector_update AFTER INSERT OR UPDATE ON "chCollectors" FOR EACH ROW EXECUTE PROCEDURE do_collector_sync();
And the function it calls:
CREATE FUNCTION "do_collector_sync"() RETURNS OPAQUE AS 'DECLARE
SELECT INTO col_id get_collector_id();
SELECT INTO col_id new."envID";
IF col_id > 0 THEN
RAISE EXCEPTION 'No ID';
END;' LANGUAGE 'plpgsql'
Now the function sync_collector(int) that is called within the function above, does in fact work perfectly.
I am having no luck troubleshooting this or locating the problem. Thanks in advance for any assistance.
Your trigger function SHOULD indeed return a value. Even if trigger functions are declared as returning OPAQUE (or TRIGGER in never versions of PostgreSQL)
From Programmers Guide:
A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for. Triggers fired BEFORE may return NULL to signal the trigger manager to skip the rest of the operation for this row (ie, subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a non-NULL value is returned then the operation proceeds with that row value. Note that returning a row value different from the original value of NEW alters the row that will be inserted or updated. It is possible to replace single values directly in NEW and return that, or to build a complete new record/row to return.
The return value of a trigger fired AFTER is ignored; it may as well always return a NULL value. But an AFTER trigger can still abort the operation by raising an error.