public inbox for [email protected]  

help / color / mirror / Atom feed

From: Nicolas Mitchell <[email protected]>
To: [email protected]
Subject: Trigger function
Date: Mon, 26 Jul 2021 15:17:12 +0000
Message-ID: <[email protected]> (raw)

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

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Trigger function
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox