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 TABLE SingleRow (
SomeNumber INT,
SomeText VARCHAR(100)
);
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
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...
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;
History
- 19th May, 2012: Initial version