Re: Me again with an insert trigger problem

Thanks.

27.02.2024 19:09:50 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

>
> 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