When creating working examples for my development site, I frequently need to restrict the minimum and maximum number of rows that can be deleted from or inserted into a table by an anonymous user.
First, create a scalar function to return the current row count.1
CREATE FUNCTION MyTableCount() RETURNS INT AS BEGIN DECLARE @RowCount INT SELECT @RowCount = COUNT(*) MyTable RETURN @RowCount END
Add a constraint to the table for the maximum number of rows.
ALTER TABLE MyTable ADD CONSTRAINT Check_Row_Max CHECK ([dbo].[MyTableCount]()<=(20))
When a user attempts to insert the 21st row, the following error is returned.
A smilar table constraint cannot be used for the minimum number of rows2, so I use an INSTEAD OF DELETE trigger.
CREATE TRIGGER dbo.Min_Row_Trigger ON dbo.MyTable INSTEAD OF DELETE AS BEGIN DECLARE @RowCount INT SELECT @RowCount = dbo.MyTableCount() IF @RowCount > 15 BEGIN DELETE T FROM DELETED D INNER JOIN dbo.MyTable T ON T.FirstName = D.FirstName AND T.LastName = D.LastName END ELSE BEGIN RAISERROR ('Minimum number of rows reached.', 11, 2) END END
This examples assumes the primary key for MyTable is FirstName-LastName. When a delete is attempted and the table contains 15 rows an error is raised.
Otherwise, the row is explicitely deleted. You could change this up and use a ROLLBACK and a FOR DELETE trigger if you prefer.
1. Check constraints cannot use sub-queries like COUNT(*). See Support subqueries for CHECK CONSTRAINTs and the aricle in the next (2) reference.
2. “CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results.” See CHECK Constraints from the MSDN library.