a trigger to check a value

Question

I have two tables :

create table building(
    id integer,
    name varchar(15),
    rooms_num integer,
    primary key(id)
);
create table room(
    id integer,
    building_id integer,
    primary key(id),
    foreign key(building_id) references building(id)
);

as you see, there is a rooms_num field in the building table which shows the number of rooms in that building and a building_id in the room table which shows that room's building. All I want is that when I insert a value into the room table , the database check itself and see if the number of room is not out of bound. is it not better to code it with a trigger? i have tried this but i dont know what to put in the condition part :

CREATE TRIGGER onRoom
ON room 
BEFORE INSERT
????

You might need to explain your intentions better. What do you mean by "room number out of bounds"? What is the number of the room? The foreign key to the building assures the building exists and the id in room is a primary key (possibly a sequence) and thus never repeated. That cannot be a number of the room within building. — Bjarni Ragnarsson, Dec 23 '19 at 16:18
don't store information that can be calculated from existing data. You don't need the rooms_num column because you can always calculate the current number from the room table. — , Dec 23 '19 at 16:19
Ah. I got stuck on the "number of room" - I suppose it should be "number of rooms"? That is - count the number of rooms registered to the building. Sorry - must be the season. — Bjarni Ragnarsson, Dec 23 '19 at 18:09
Here is an answer to a similar question. Many more around. https://stackoverflow.com/questions/1743439/how-to-write-a-constraint-concerning-a-max-number-of-rows-in-postgresql — Bjarni Ragnarsson, Dec 23 '19 at 18:14

score 0 · Accepted Answer · answered Dec 23 '19 at 18:44

First you should tighten up your data model. Everything that should be present should be marked NOT NULL such as the building's name. I tend to like making sure required text fields have actual values in them, so I put a check constraint in that matches at least one "word" character (alphanumeric).

You should not ever be allowing negative room numbers, right? In addition, you should avoid using private database information like a room number or building number as a primary key. It should be considered a candidate key only. Best practice in my opinion would be to use UUIDs for primary keys, but some people love their auto-incrementing integers, so I won't push here. The point is that rooms tend to (for example) get drywall put up separating them or taken down to make bigger spaces. Switching around primary key IDs can have unexpected results. Better to separate how its identified within the database from the data itself so you can add "Room 6A".

It should also be a safe bet that you won't have more than 32,767 rooms per building, so int2 (16-bit, 2-byte integer) would work here.

CREATE TABLE building (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar(15) NOT NULL UNIQUE CHECK (name ~ '\w'),
    rooms_num int2 NOT NULL CHECK (rooms_num >= 0)
);

CREATE TABLE room (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- Should there be a room name too?
    room_id int2 NOT NULL CHECK (room_id > 0),
    building_id integer NOT NULL REFERENCES building (id),
    UNIQUE (room_id, building_id)
);

Okay, now you have a more solid foundation to work from. Let's make the trigger.

CREATE FUNCTION room_in_building ()
RETURNS TRIGGER LANGUAGE plpgsql STRICT STABLE AS $$
BEGIN
  IF (
    -- We can safely just check upper bounds because the check constraint on
    -- the table prevents zero or negative values.
    SELECT b.rooms_num >= NEW.room_id
    FROM building AS b
    WHERE b.id = NEW.building_id
  ) THEN
    RETURN NEW; -- Everything looks good
  ELSE
    RAISE EXCEPTION 'Room number (%) out of bounds', NEW.id;
  END IF;
END;
$$;

CREATE TRIGGER valid_room_number
  BEFORE INSERT OR UPDATE -- Updates shouldn't break the data model either
  ON room
  FOR EACH ROW EXECUTE PROCEDURE room_in_building();

You should also add an update trigger for the building table. If someone were to update the rooms_num column to a smaller number, you could end up with an inconsistent data model.

a trigger to check a value

1 Answers1