I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as below...
ALTER TRIGGER OnlyOneConfigRow ON [dbo].[Configuration] INSTEAD OF INSERT AS BEGIN DECLARE @HasZeroRows BIT; SELECT @HasZeroRows = CASE WHEN COUNT (Id) = 0 THEN 1 ELSE 0 END FROM [dbo].[Configuration]; IF EXISTS(SELECT [Id] FROM inserted) AND @HasZeroRows = 0 BEGIN RAISERROR ('You should not add more than one row into the config table. ', 16, 1) END END
This does not throw an error but is not allowing the first row to go in.
Also is there a more effective / more self explaining way of limiting the number of rows that can be inserted into a table to just 1, than this? Am I missing any built in SQL Server feature?