1

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');
  • 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. – John Eisbrener Oct 3 '17 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. – John Eisbrener Oct 3 '17 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 '17 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 '17 at 8:22
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)

CRAZY

  • The insert statement I am using works against PRE_CASE on an untouched DB. On the altered DB it fails on PRE_CASE (the view) and works on PRE_TCASE (the table). I think i failed to properly explain the situation in full, I apologise and will edit the question. – Paul Oct 3 '17 at 14:56
  • @Paul, I've updated my answer after googling it. – Tara Kizer Oct 3 '17 at 15:08
  • As I say ID is part of a primary key and thus I can not set it to NULL – Paul Oct 4 '17 at 6:44
  • Nor can an identity column be set to accept NULLs – Paul Oct 4 '17 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 '17 at 8:27
0

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, privacy policy and cookie policy

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