• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 930
  • Last Modified:

Set Identity_insert <table> on

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.  
1 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.

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now