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

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