Trigger function

Hi,

I have a set of tables, ‘object’, ‘obtype’, ‘host’ and
domain:

CREATE TABLE "domain"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000001 INCREMENT
BY 1),
"name" VARCHAR(64) NOT NULL,
CONSTRAINT "domain__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "obtype"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 2800001 INCREMENT
BY 1),
"name" VARCHAR(16) NOT NULL,
CONSTRAINT "obtype__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "host"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 2900001 INCREMENT
BY 1),
"name" VARCHAR(32) NOT NULL,
"domain" INTEGER NOT NULL,
"object" INTEGER NOT NULL,
CONSTRAINT "host__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "object"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 3200001 INCREMENT
BY 1),
"type" INTEGER NOT NULL,
CONSTRAINT "object__id__pk" PRIMARY KEY ("id")
);

ALTER TABLE "domain" ADD CONSTRAINT "domain__name__uk" UNIQUE ("name");

ALTER TABLE "obtype" ADD CONSTRAINT "obtype__name__uk" UNIQUE ("name");

ALTER TABLE "host" ADD CONSTRAINT "host__name_domain__uk" UNIQUE
("name","domain");

ALTER TABLE "host" ADD CONSTRAINT "host__object__uk" UNIQUE ("object");

ALTER TABLE "host" ADD CONSTRAINT "host__object__fk" FOREIGN KEY
("object") REFERENCES "object" ("id") ON DELETE CASCADE;

ALTER TABLE "host" ADD CONSTRAINT "host__domain__fk" FOREIGN KEY
("domain") REFERENCES "domain" ("id") ON DELETE RESTRICT;

ALTER TABLE "object" ADD CONSTRAINT "object__type__fk" FOREIGN KEY
("type") REFERENCES "obtype" ("id") ON DELETE RESTRICT;

I am looking at whether functions can help me automate creating a new
object when a new host is added.

I can do this manually with the following code:

WITH object_id AS
(INSERT INTO public.object (type)
VALUES (
( SELECT obtype.id
FROM public.obtype
WHERE obtype.name LIKE 'host'
)
)
RETURNING id)
INSERT INTO host (name, domain, object)
VALUES ('gary', 1000001, (SELECT * FROM object_id));

I have a number of questions but I would like to begin by asking whether
this a candidate for a trigger function on table ‘host’, triggered
before an insert?

Many thanks,

NicM