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');
Best Answer
At first I thought this was due to the
INSERT INTO
pre_case that you are doing, not theINSERT 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 theINTEAD 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.CRAZY