I'm dealing with a 3rd party app that I can't change or get support on. I've altered an underlying table in several ways to enhance it's functionality

1- I renamed the table PRE_CASE to PRE_TCASE
2- I created a view named PRE_CASE which is a select on to PRE_TCASE with a UNION ALL on to a linked server DB and table named OTHERSYSTEM_CASE

This works to the extent the app is now fooled into displaying data from PRE_TCASE and OTHERSYSTEM_CASE to the end user when it believes it's looking at the table PRE_CASE. It fails however on inserting data. To try and fix this I created a trigger (I only require to insert to PRE_TCASE, never OTHERSYSTEM_CASE)

   ON  dbo.pre_case

    (col1, col2, col3, etc.)
    SELECT col1, col2, col3, etc.
    FROM Inserted


I am getting failures on insert with the error:

Msg 233, Level 16, State 2, Line 1 The column 'ID' in table 'dbo.pre_case' cannot be null.

The insert statement is of the form

INSERT INTO dbo.pre_case (col1, col2, col3)
values (1,2,3)

No value/reference is made to the ID column.

On the underlying PRE_CASE the column ID is an integer primary key (PK, int, not null) with a valid identity seed. Direct inserts onto PRE_CASE work, but not ones onto the new view and trigger. What have I overlooked?

(My end solution will be deployed onto SQL2000, SQL2005 and SQL2008R2 boxes, but I'm testing/developing on SQL2000)

Edit: I created this model to test in a more isolated way. Turns out the error occurs on SQL2000 and not SQL2008R2, personally I'm giving up at this point.

create table t1(id int  IDENTITY(1,1) PRIMARY KEY ,ltext varchar(100));
create table t2(id int IDENTITY(1,1) PRIMARY KEY,ltext varchar(100));

SELECT id, ltext from t1
union all
SELECT id, ltext from t2

select * from t2;
select * from tv;

INSERT  t1(ltext) values ('test1a');
INSERT  t1(ltext) values ('test1b');
INSERT  t1(ltext) values ('test1c');
INSERT  t2(ltext) values ('test2a');
INSERT  t2(ltext) values ('test2b');
INSERT  t2(ltext) values ('test2c');  

INSERT t2(ltext)
select ltext from inserted


-- this errors on SQL2000
INSERT  tv(ltext) values ('testv-a');
  • Is the underlying PRE_TCASE table setup with the IDENTITY field? Is this the table you're trying to insert into? If you're trying to insert into an IDENTITY field, you need to set IDENTITY_INSERT to ON for the table in question. Oct 3, 2017 at 15:02
  • Also, Explicit values cannot be inserted into identity columns in remote tables. per Guidelines for Using Distributed Queries, so you may need to set this up in a different way entirely if you're reliant upon a Distributed, Partitioned View. Oct 3, 2017 at 15:04
  • @JohnEisbrener yes the PRE_TCASE has a primary key identity column. I can't alter the insert statement as it's formulated by the vendor supplied executable, it's not trying to supply the identity value.I did play around however and identity insert did not help.
    – Paul
    Oct 4, 2017 at 8:21
  • @JohnEisbrener I'm not trying to insert identity values at all. Nor am I trying to insert anything in a remote table. The target table is local to the database.
    – Paul
    Oct 4, 2017 at 8:22

2 Answers 2


At first I thought this was due to the INSERT INTO pre_case that you are doing, not the INSERT INTO trigger. But that turned out not to be the case after more info was supplied.

Judging by the google results, this is a common error with INSTEAD OF triggers and presumably an identity column. The value for that column has not been generated when the INTEAD OF trigger fires, so it's NULL from ID. Change the column to be nullable and then see what you get in the ID column in the new table. If you have a value for it, then you are good to go. If you don't, it seems you'll need to generate it.

(My end solution will be deployed onto SQL2000, SQL2005 and SQL2008R2 boxes, but I'm testing/developing on SQL2000)


  • As I say ID is part of a primary key and thus I can not set it to NULL
    – Paul
    Oct 4, 2017 at 6:44
  • Nor can an identity column be set to accept NULLs
    – Paul
    Oct 4, 2017 at 7:09
  • Looks like a SQL2000 issue, as per my edit just now, I think I give up. Partly as I don't think I can solve it, partly as it has to work on SQL2000 to be of value to me.
    – Paul
    Oct 4, 2017 at 8:27

This can't be made to work under SQL2000. If you provide a dummy ID value:

INSERT  tv(id, ltext) values (0, 'testv-a');

Then it will work, the 0 will be ignored and an identity value generated. But as the question scenario states the insert statement is set in stone by the 3rd party executable then it is unworkable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.