Problems with insert trigger and primary key

Question

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)

create TRIGGER INSREPLACE
   ON  dbo.pre_case
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.PRE_TCASE
    (col1, col2, col3, etc.)
    SELECT col1, col2, col3, etc.
    FROM Inserted

END

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));

CREATE VIEW tv AS
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');  



CREATE TRIGGER TRG_TV ON tv
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT t2(ltext)
select ltext from inserted

END

-- this errors on SQL2000
INSERT  tv(ltext) values ('testv-a');

Answer 1

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)

CRAZY

Answer 2

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.