Set Identity_insert <table> on

Posted on 2005-05-11
Last Modified: 2010-05-18
What I am trying to do is to stop inserts into a table if the Set Identity_insert <table> on command is not run.  I have a situation where we have central tables that replicate out to many remote servers.  We want the keys in these tables to be the same.  We also want the same schema in all the tables so we always no that all the databases are in sync.  We are not using sql server replication to do this but have created our own.  In our replication where update the remote table we Set Identity_insert <table> on before the instert and Set Identity_insert <table> off after the instert.  We want to create triggers on these tables so that a remote site can't update the local table unless Set Identity_insert <table> on was issued.  

Is there a way to query inside the trigger if the Set Identity_insert <table> on for that table has been issued.  
Question by:curtis591
    1 Comment
    LVL 17

    Accepted Solution

    I don't know if there is a direct way, but you might be able to do it indirectly.
    If identity_insert is ON, then the insert statement must specifiy an explicit value for the identity, or an error is given.
    When the identity is OFF then the insert statement must NOT specify a value, and then an error is given.

    you can create an instead-of trigger on your table, which does an explicit insert of the identity column into the table from the inserted table. If set identity_insert is OFF for the table, the trigger will fail.

    as in

    create trigger xyztrig on xyz
    instead of insert
    insert into xyz (a,b) select a,b from inserted

    now, if identity is set OFF, the trigger will fail to execute.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now