Re: Me again with an insert trigger problem

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(dot)klaver(at)aklaver(dot)com