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 TABLE SingleRow (
And add a single row into it:
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.
CREATE TRIGGER SingleRow_Trigger
INSTEAD OF INSERT, DELETE
RAISERROR ( N'Table can contain only 1 row', 16, 1) WITH SETERROR;
So the trigger above fires upon
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...
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:
DISABLE TRIGGER SingleRow_Trigger ON SingleRow;
DELETE FROM SingleRow;
ENABLE TRIGGER SingleRow_Trigger ON SingleRow;
- 19th May, 2012: Initial version