IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
Bevo
I don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo
|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:

> I don't believe a trigger will work for you either Bevo. You are trying to
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> the
> something
>
>
|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.

> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.

> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.

> I cannot modify the application code insert.
!!? Even when it won't work? That seems a bizzare and unreasonable.
David Portas
SQL Server MVP
|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
David Portas
SQL Server MVP
|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
David Portas
SQL Server MVP
|||Why you ask. Well, the application is currently supported on MySQL and
Oracle, we are attempting to adopt SQL Server support.
There have been many issues which required application code and database
changes to support SQL Server. For example, the rowsize and index size limits
required changes. Reserved words, such as IDENTITY, and the lack of support
for key SQL functions, like CONCAT (posting on this as well). There are many
others...
This particular issue of the INSERT statement would require extensive
application changes, we view that solution as a last resort.
"David Portas" wrote:

> cannot
> So presumably something has changed since the application went into
> production (I'm assuming that it worked at one time)? What was changed and
> why? If you explain what you are actually trying to achieve then maybe
> someone can suggest some alternatives that won't break your application
> code. It appears that the problem isn't in the INSERT statement but
> elsewhere.
> --
> David Portas
> SQL Server MVP
> --
>
>