none
how to configure table to hold only 10 raw ?

    Question

  • hi...

    I need to configure table to hold 10 raws ... if try to insert raw more then 10 raw in the table then  if should refuse to insert .

    how to do that ?


    Saturday, October 05, 2013 6:27 PM

Answers

All replies

  • Hi,

    You can implement an after insert trigger on the table with check on number of rows in the table. For example:

    CREATE TRIGGER TriggerName ON [dbo].[TableName]
    FOR INSERT
    AS
    BEGIN
    
    	if (select count(t.PKName) from dbo.TableName t) > 10
    	begin
    		raiserror ('Number of rows limited to 10.', 16, 1)
    	end
    END


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    Saturday, October 05, 2013 6:44 PM
  • Just wanted to add to what Dean suggested. Please note that RASERROR should be followed by a ROLLBACK TRAN statement to ensure that the insert fails completely. Without the ROLLBACK statement, an error is raised, but the INSERT will successfully complete.


    My Blog

    Saturday, October 05, 2013 7:08 PM
  • Hi

    You can use combination of Check constraint + identity + unique constraint to limit the number of record for a table,

    create table table1
    ( id int identity(1,1) ,
     name varchar(10),
     CONSTRAINT UK_ID UNIQUE(id) 
    )
    GO
    ALTER TABLE dbo.table1 ADD CONSTRAINT CK_limit
        CHECK (id < 11)
     
    -- truncate table table1   
        insert into table1(name)
        select '1' union all
        select '2' union all
        select '3' union all
        select '4' union all
        select '5' union all
        select '6' union all
        select '7' union all
        select '8' union all
        select '9' union all
        select '10' 
     GO
        insert into table1(name)   
        select '11' 
        GO
      select * From table1  

    Thanks

    Saravana Kumar C

    Saturday, October 05, 2013 7:18 PM
  • You can define a SEQUENCE with that characteristics and connect it to the table:

    BOL: "CREATE SEQUENCE Test.DecSeq
        AS decimal(3,0)
        START WITH 125
        INCREMENT BY 25
        MINVALUE 100
        MAXVALUE 200
        CYCLE
        CACHE 3
    ;"

    Link: http://technet.microsoft.com/en-us/library/ff878091.aspx


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Saturday, October 05, 2013 7:59 PM
  • CREATE TABLE Foobar
    (foo_id INTEGER NOT NULL PRIMARY KEY,
     foo_cnt SMALLINT DEFAULT 1 NOT NULL UNIQUE
       CHECK (foo_cnt BETWEEN 1 ANDd 10), 
     ..);

    Read a book on RDBMS and learn Codd's Information Principle. 
     
      



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, October 05, 2013 11:46 PM