Thanks. 27.02.2024 19:09:50 Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.com
