How to detect IDENTITY_INSERT ON

I have an INSTEAD OF INSERT trigger on a table with an identity column. When
I insert the actual row in the trigger, I need to know if IDENTITY_INSERT has
been set for the table in order to issue the correct INSERT statement. Is
there a function that tells me if IDENTITY_INSERT is currently ON for a table?
Thanks,
Tom
Hi Tommy,
SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
Replace table with your table name.
Thanks
Yogish
|||That will only show whether the table *has* an identity column, not whether IDENTITY_INSERT is
turned on or not. AFAIK, this information is not exposed. I tried DBCC USEROPTIONS, but that doesn't
expose the information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:B091381A-3D58-4512-A364-754A4E0B8BD9@.microsoft.com...
> Hi Tommy,
> SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
> Replace table with your table name.
> --
> Thanks
> Yogish
|||Hi Tibor,
Yeah, you are right. I realised it after posting the message. And DBCC
USEROPTIONS doesn't give this option.
Thanks
Yogish
|||Hi Tommy,
Check out the remarks from BOL.
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a SET
IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
Server? returns an error message that states SET IDENTITY_INSERT is already
ON and reports the table it is set ON for.
Run the following...
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
SET IDENTITY_INSERT products_new ON
On the second statement,
IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
SET operation for table 'products_new'.
I hope this will answer your question in an indirect way.
Thanks
Yogish
|||It's true that I'll get an error if I try to set IDENTITY_INSERT on for
another table, but while I can capture the error code, I can't capture the
error message. So I know that IDENTITY_INSERT is on for another table, but I
don't know which table.
The closest solution I've found is to query the INSERTED pseudo-table. If
IDENTITY_INSERT is off, then the identity value will be zero for every row in
INSERTED. If IDENTITY_INSERT is on, then INSERTED will have other values,
unless the triggering statement is explicitly inserting zeroes.
"Yogish" wrote:

> Hi Tommy,
> Check out the remarks from BOL.
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a SET
> IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
> Server? returns an error message that states SET IDENTITY_INSERT is already
> ON and reports the table it is set ON for.
> Run the following...
> CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> SET IDENTITY_INSERT products ON
> GO
> SET IDENTITY_INSERT products_new ON
> On the second statement,
> IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
> SET operation for table 'products_new'.
> I hope this will answer your question in an indirect way.
> --
> Thanks
> Yogish
>