How to Guarantee a Fixed Amount of Rows in a Table

Introduction

Occasionally, you may need to have a fixed amount of rows in a single table. For example, you may have a configuration type of table which should contain only one row and applications rely on that fact. To ensure that the amount of rows isn't changed, you can use a trigger to prevent insertions and deletions.

Implementation

Let's first create a table with one row. The table is as follows:

-- Create the test table
CREATE TABLE SingleRow (
   SomeNumber INT,
   SomeText   VARCHAR(100)
); 

And add a single row into it:

-- Add a single row
INSERT INTO SingleRow (SomeNumber, SomeText) VALUES (1, 'A');

After adding the desired rows (one row in this case), let's add a trigger on the table.

-- Define the trigger
CREATE TRIGGER SingleRow_Trigger
ON SingleRow
INSTEAD OF INSERT, DELETE
AS BEGIN
   RAISERROR ( N'Table can contain only 1 row', 16, 1) WITH SETERROR;
END;  

So the trigger above fires upon INSERT and DELETE. The sole purpose of the trigger is to generate an error if the amount of data is about to change.

Note that the trigger is defined as INSTEAD OF. This means that the actual operation isn't done. If the trigger would be an AFTER trigger, it should contain also a ROLLBACK statement to prevent the modification to be committed.

So what happens now if the data is being modified. Executing the following statement...

-- Try to add a row
INSERT INTO SingleRow (SomeNumber, SomeText) VALUES (2, 'B');  

...produces an error message like the next one:

Msg 50000, Level 16, State 1, Procedure SingleRow_Trigger, Line 5
Table can contain only 1 row 

The same error message is received if a DELETE statement is issued.

What If Changes Need to be Done Momentarily

Sometimes you may need to make changes to the amount of rows. This hardly applies to a single row scenario but in other cases the total amount of rows may need to be changed.

The easiest way to do this is to use a DISABLE TRIGGER statement, make the modifications and then enable the trigger again.

So deleting the example row in the table succeeds with the following snippet:

-- Delete the row
-- Disable the trigger
DISABLE TRIGGER SingleRow_Trigger ON SingleRow;

-- Delete the row
DELETE FROM SingleRow;

-- Enable the trigger
ENABLE TRIGGER SingleRow_Trigger ON SingleRow;

History

• 19^th May, 2012: Initial version