How can I only enter a certain amount of records into sql server?

Question

I have a table called Hoteis which is related to another helper table Hoteis_Fotos, as its name suggests this table will save the photos of a particular hotel. As you may have understood, each hotel can have more than one photo, but a maximum of 10 photos per hotel.

Assuming the structure of my table Hoteis_Fotos be the following:

create table Hoteis_Fotos( id_hotel_foto int not null primary key identity, fk_hotel int not null, -- faz referência ao id do hotel na tabela 'Hoteis' foto varbinary(max) )

How do I add a constraint (or something like that) that makes it possible to insert only 10 photos per hotel into the table Hoteis_Fotos ?

Answer 1

Good afternoon,

Create the tables below:

create table Hoteis
(
  id_hotel int not null primary key identity
);

create table Hoteis_Fotos(
   id_hotel_foto int not null primary key identity,
   fk_hotel int not null,
   foto varbinary(max),
   foreign key(fk_hotel) references Hoteis(id_hotel)
);

After creating, create a stored Procedure for the database itself to execute the validation task, if direct to the database, if no. Net (C#, VB), store a variable that performs the query in the database (Ado.Net) with command:

select count(*) from Hoteis_Fotos where fk_hotel = valor informado;

Any questions I’m willing to help you

Answer 2

You can create a TRIGGER which will check the current amount of records in the table hoteis_fotos and if you identify that there are already 10 records, it will cause an error with the message that the number of records has been exceeded:

IF OBJECT_ID('tr_hoteis_fotos_i', 'TR') IS NULL
BEGIN
  EXEC('CREATE TRIGGER tr_hoteis_fotos_i ON hoteis_fotos INSTEAD OF INSERT AS BEGIN END;');
END;
GO

ALTER TRIGGER tr_hoteis_fotos_i
ON hoteis_fotos
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @fk_hotel      INT,
          @foto          VARBINARY(MAX);

  DECLARE cursor_fotos CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT ins.fk_hotel,
           ins.foto
      FROM inserted ins
  OPEN cursor_fotos;
  FETCH NEXT FROM cursor_fotos
  INTO @fk_hotel,
       @foto;
  WHILE @@FETCH_STATUS = 0  
  BEGIN
    DECLARE @quantidade INT,
            @mensagem   VARCHAR(1000);

    SELECT @quantidade = COUNT(1)
      FROM hoteis_fotos hf WITH(NOLOCK)
     WHERE hf.fk_hotel = @fk_hotel;

    IF @quantidade >= 3
    BEGIN
      SET @mensagem = 'Quantidade de registros excedida para o hotel de código ' + CAST(@fk_hotel AS VARCHAR);
      RAISERROR(@mensagem ,16, 1);
    END
    ELSE
    BEGIN
      INSERT INTO hoteis_fotos(fk_hotel,
                               foto)
      VALUES(@fk_hotel,
             @foto);
    END;

    FETCH NEXT FROM cursor_fotos   
    INTO @fk_hotel,
         @foto;
  END;
  CLOSE cursor_fotos;  
  DEALLOCATE cursor_fotos;
END;
GO