Click here to Skip to main content
Click here to Skip to main content

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

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

  • 19th May, 2012: Initial version

License

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

Share

About the Author

Mika Wendelius
Architect
Finland Finland
I've been a programmer since mid 80's using languages like assembler, C/C++, PL/I (mainframe environment), pascal, VB (I know, I know, no comments please) and C# and utilizing different techniques and tools.
 
However I'm specialized in databases and database modeling. Mostly I have used products like Oracle (from version 6), SQL Server (from version 4.2), DB2 and Solid Server (nowadays an IBM product).
 
For the past 10+ years my main concerns have been dealing with different business processes and how to create software to implement and improve them. At my spare time (what ever that actually means) I'm also teaching and consulting on different areas of database management, development and database oriented software design.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 19 May 2012
Article Copyright 2012 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid