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