Search code examples
t-sqlvalidationconcept

Check if only single record exists


I have a table with

GID
ID
DefaultA
DefaultB
DefaultC
DefaultD
DefaultE
DefaultF

my requirement is that there should always be one record in the database always.

In the front end if the end user tries to update its ok since he will update the existing record.But if he tries to enter a new record I must stop him.


Solution

  • Eliminate all Insert, delete permissions from all users...

    or,

    Add a trigger that rolls back any transaction that attempts to delete or insert a record.

    Create Trigger MyTableEnsureSingleRowTrigger 
    On MyTable for Insert, Delete 
    As Rollback Transaction