SQL Server trigger: limit record insert

Question

I'm doing a database for a university work. At certain point we have to create a trigger/function that can limit insertion of records on my SQL Server Management Studio 17, taking into account different foreign keys. For example we can have 100 records, but we can only have 50 records for the same foreign key.

We have this constraint:

CREATE TABLE [dbo].[DiaFerias] WITH CHECK 
    ADD CONSTRAINT [Verificar22DiasFerias] 
         CHECK (([dbo].[verificarDiasFerias]((22)) ='True'))

With the help of this function:

ALTER FUNCTION [dbo].[verificarDiasFerias] (@contagem INT) 
RETURNS VARCHAR(5) 
AS 
BEGIN 
    IF EXISTS (SELECT DISTINCT idFuncionario, idDiaFerias 
               FROM DiaFerias 
               GROUP BY idFuncionario, idDiaFerias 
               HAVING COUNT(*) <= @contagem) 
        RETURN 'True' 

    RETURN 'False' 
END

I'm not sure what that function is meant to do but I'm almost certain it's not doing it right. And potentially expensively querying the entire table for any insert/update is a bad plan. (Hint - this function currently says "Everything is fine so long as at least *one combination* of `idFuncionario, idDiaFerias` has fewer than 23 rows") — Damien_The_Unbeliever, Jul 12 '19 at 10:31

score 2 · Answer 1 · answered Jul 12 '19 at 10:49

Would I use a trigger here? Reluctantly, yes (See Enforce maximum number of child rows for how to do it without triggers, in a way I'd never recommend). Would I use that function? No.

I'd create an indexed view:

CREATE VIEW dbo.DiaFerias_Counts
WITH SCHEMABINDING
AS
    SELECT idFuncionario, idDiaFerias, COUNT_BIG(*) as Cnt
    FROM dbo.DiaFerias
    GROUP BY idFuncionario, idDiaFerias
GO
CREATE UNIQUE CLUSTERED INDEX PK_DiaFerias_Counts on
    dbo.DiaFerias_Counts (idFuncionario, idDiaFerias)

Why do this? So that SQL Server maintains these counts for us automatically, so we don't have to write a wide-ranging query in the triggers. We can now write the trigger, something like:

CREATE TRIGGER T_DiaFerias
ON dbo.DiaFerias
AFTER INSERT, UPDATE
AS
    SET NOCOUNT ON;

    IF EXISTS (
      SELECT
         *
      FROM dbo.DiaFerias_Counts dfc
      WHERE
        dfc.Cnt > 22
      AND
        (EXISTS (select * from inserted i
         where i.idFuncionario = dfc.idFuncionario AND i.idDiaFerias = dfc.idDiaFerias)
        OR EXISTS (select * from deleted d
         where d.idFuncionario = dfc.idFuncionario AND d.idDiaFerias = dfc.idDiaFerias)
      )
    )
    BEGIN
        RAISERROR('Constraint violation',16,1)
    END

Hopefully you can see how it's meant to work - we only want to query counts for items that may have been affected by whatever caused us to trigger - so we use inserted and deleted to limit our search.

And we reject the change if any count is greater than 22, unlike your function which only starts rejecting rows if every count is greater than 22.

I'll try that @Damien_The_Unbeliever, thanks for the understanding and pacience. Best regards. — João Costa, Jul 12 '19 at 11:33
@JoãoCosta - that's because the sample in your question seemed to want to consider both idFuncionario and idDiaFerias and be counting unique combinations of those against a limit. If you just want the limit on one of those columns, remove the other one in the obvious places. — Damien_The_Unbeliever, Jul 13 '19 at 15:34
i've found another solution that seemed to work. Thanks anyway, grateful for your help! — João Costa, Jul 13 '19 at 21:02

SQL Server trigger: limit record insert

1 Answers1