Click here to Skip to main content
13,452,986 members (53,729 online)
Click here to Skip to main content
Add your own
alternative version


2 bookmarked
Posted 19 May 2012

How to Guarantee a Fixed Amount of Rows in a Table

, 19 May 2012
Rate this:
Please Sign up or sign in to vote.
This tip describes how to guarantee a fixed amount of rows in a table by using triggers


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.


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

-- Create the test table
   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
   RAISERROR ( N'Table can contain only 1 row', 16, 1) WITH SETERROR;

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

-- Enable the trigger
ENABLE TRIGGER SingleRow_Trigger ON SingleRow;


  • 19th May, 2012: Initial version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Finland Finland
No Biography provided

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01-2016 | 2.8.180318.3 | Last Updated 19 May 2012
Article Copyright 2012 by Wendelius
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid