Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- cleaned setup
CREATE TABLE table1 (
  id        serial PRIMARY KEY
, jobs_ids  int4[]
, status    int4
);

CREATE TABLE job (
  id           serial PRIMARY KEY
, type_id      int NOT NULL DEFAULT 1  -- was missing
, description  varchar
, created_at   timestamptz DEFAULT now()
, modified_at  timestamptz DEFAULT now()
);

-- dummy row
INSERT INTO table1 (jobs_ids, status)
VALUES ('{}', 2);

CREATE TABLE



CREATE TABLE



INSERT 0 1


-- radically simpler trigger function
CREATE OR REPLACE FUNCTION new_job_id_table1()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO job (description, type_id)
   VALUES ('automated jobid with id=' || NEW.id, 1)
   RETURNING ARRAY[id]
   INTO   NEW.jobs_ids;
   
   RETURN NEW; 
END
$func$;

-- trigger only when condition is met
CREATE TRIGGER job_id_trigger_table1
BEFORE UPDATE OF status ON table1
FOR EACH ROW
WHEN (NEW.status = 3
  AND NEW.jobs_ids = '{}')  -- !
EXECUTE FUNCTION new_job_id_table1();

TABLE table1;
TABLE job;

CREATE FUNCTION



CREATE TRIGGER




id
jobs_ids
status


1
{}
2



SELECT 1




id
type_id
description
created_at
modified_at



SELECT 0


UPDATE table1
SET    status = 3
WHERE  id = 1;

UPDATE 1


TABLE table1;
TABLE job;


id
jobs_ids
status


1
{1}
3



SELECT 1




id
type_id
description
created_at
modified_at


1
1
automated jobid with id=1
2022-10-05 18:52:53.374414+01
2022-10-05 18:52:53.374414+01



SELECT 1