Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Unanswered: Trigger Problem (about to go crazy)

    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
    col_id:=0;
    BEGIN
    /*
    SELECT INTO col_id get_collector_id();
    */
    SELECT INTO col_id new."envID";
    IF col_id > 0 THEN
    PERFORM sync_collector(col_id);
    ELSE
    RAISE EXCEPTION 'No ID';
    END IF;
    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.

    -Benjamin

  2. #2
    Join Date
    Feb 2003
    Location
    Denmark
    Posts
    7
    Hi Benjamin

    You don't say what your problem is. Do you get an error? On creation-time or execution time?

  3. #3
    Join Date
    Apr 2003
    Posts
    3
    Actually it simply wasn't giving an error. It just did nothing.

    To fix it, I added:

    RETURN new;

    in my Functions returning OPAQUE

  4. #4
    Join Date
    Feb 2003
    Location
    Denmark
    Posts
    7
    Hi Benjamin

    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.

  5. #5
    Join Date
    Apr 2003
    Posts
    3
    Thanks!

  6. #6
    Join Date
    Apr 2003
    Location
    Hungary, Budapest
    Posts
    1

    Re: Trigger Problem (about to go crazy)

    You must return form a function with a row...
    usually:
    RETURN NEW;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •