Re: Me again with an insert trigger problem - Mailing list pgsql-general

On 2/27/24 9:49 AM, Thiemo Kellner wrote:

Hi

I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD.

I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is very simple. Only records of node type "Drop-off" must and are allowed to have a task name. If this is not given, raise an exception. What am I missing?

Function code:
    create or replace function NODE_GOOD⠒TR_B_IU_R()
      returns trigger
      language plpgsql
      stable
      set search_path = SNOWRUNNER,
                        PUBLIC
      as
    $body$
        declare
            V⠒NODE_TYPE⠒NAME                NODE⠒V.NODE_TYPE⠒NAME%type := null;
        begin
            -- raise info ': %', ;
            raise info 'new.NODE⠒ID: %', new.NODE⠒ID;
            raise info 'new.TASK_NAME: %', new.TASK_NAME;
            select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME
              from NODE⠒V
             where 1 = 1
               and ID = new.NODE⠒ID
               and 1 = 1;
            raise info 'V⠒NODE_TYPE⠒NAME: %', V⠒NODE_TYPE⠒NAME;
            if (    V⠒NODE_TYPE⠒NAME = 'Drop-off'
                and new.TASK_NAME is null) then
                raise exception 'A good connection to a drop-off node must have a task name!';
            elsif (    V⠒NODE_TYPE⠒NAME != 'Drop-off'
                   and new.TASK_NAME is not null) then
                raise exception 'A good connection to a non-drop-off node cannot have a task name!';
            end if;
            raise info 'Going to leave the trigger function "NODE_GOOD⠒TR_B_IU_R"';
            return null;

Assuming this is row level trigger and run BEFORE:

https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER

"

Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value.

"

        end;
    $body$;


The output of the important part of the install script is listed at the end.

Please find attached my code.

Kind regards

Thiemo


insert data into NODE_GOOD⠒V
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 1107cb8d-c1f1-4368-ac7b-72ac3031555a
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: Landslide on the Highway
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Drop-off
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: cdb25b50-e6cf-46fe-85f6-47ec72c00a22
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 59dec625-9167-4e63-9022-917e1a751206
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: b4fd810a-2065-4bcc-bd1d-49021d7ade95
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: a3459f1d-2615-4b20-946b-daca4a9e69de
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: c0069eea-0ee0-44ca-8b15-c14e59230a75
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 5917e5d2-bc16-4126-8486-6a8bedca45aa
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 86abd010-d930-4486-9a5e-1e85d8e81faa
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: b80adef3-8233-4e20-8f8e-3a5ccf04aacd
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: f51c6a96-ffbb-433b-8402-2b4dc467b689
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 37e29f40-9da0-44e7-a601-06dfa94043e6
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: e8d2c14d-37bd-4c11-a3c6-55cd382fd414
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
INSERT 0 0
COMMIT

-- 
Adrian Klaver
adrian.klaver@aklaver.com