Ensure there is one row in a Table

How to ensure there is only one row in a table

There are situation where you want your table to contain only one row, generally such tables are used to store some configuration information, and you want :

• No one even accidently can delete the rows in it
• No one can enter new record to that table

If such is your requisite then we can achieve your requirement by implementing “Instead Of Triggers” on Table, let see how we are implementing our solution:

We will simulate the whole problem by taking example; we create a table named OneRowTable then insert single record in it:

create table OneRowTable

(id int identity(1,1),

name varchar(10) )

Command(s) completed successfully.

insert into OneRowTable values('Siddhi')

(1 row(s) affected)

Now we try to create Insert Preventive Trigger on OneRowTable, so that when anyone try to insert new record, it will not allow to insert and prompt a message.

create trigger  tr_insert_prevent

ON OneRowTable INSTEAD OF INSERT

AS

begin

declare @cnt int

      select @cnt=count(*) from OneRowTable

      if @cnt > 0

      begin

            raiserror('The table must always have only one row',1,1)                  

      end

      else

      begin

            insert into OneRowTable

            select i.name from inserted i

      end  

end

GO

Command(s) completed successfully.

Insertion after Trigger

Now since we have created this trigger which prevent user to enter new row to the table, it should give error message while someone tries to insert new record. Let’s check out

   

insert into OneRowTable values('Kyasti')

The table must always have only one row.

Msg 50000, Level 1, State 1

(1 row(s) affected)

We can see as we go for inserting new row, trigger cam into action and prevent the inserting of record.

Deletion after Trigger

Since we have Trigger in place, it will restrict the user from deleting rows from OneRowTable, let’s have a look to it

delete OneRowTable

The table must always have only one row.

Msg 50000, Level 1, State 1

(1 row(s) affected)

So you can see it prevent me from deleting record.

Coding is what coders know “BLISS”

Post Reference: Vikram Aristocratic Elfin Share