Hi
I am using SQL Server 2000 developer edition with SP4 running on Windows
2000 Pro SP4.

I have two tables as follows:-

CREATE TABLE CURRENTTB
( id int NOT NULL
,ColA nvarchar(10) NOT NULL
,ColB nvarchar(5) NOT NULL
)

CREATE TABLE ARCHIVETB
( id int NOT NULL
,ColA nvarchar(10) NOT NULL
,ColB nvarchar(5) NOT NULL
)

I have a view as follows:-

CREATE VIEW CURRENTDATA
AS
   select id, ColA, ColB from CURRENTTB c
    where not exists (select 1 from ARCHIVETB
                             where ColA = c.ColA
                             and ColB = c.ColB)
WITH CHECK OPTION

When I attempt to insert data using the following SQL, it fails with the
internal error:

      insert into CURRENTDATA (ID, ColA, ColB)
      select max(ID) +1, 'Val1', 'Val2' from CURRENTDATA

Nothing appears in the event log.


If I use the following SQL it works and I get the correct error due to
violation of the CHECK OPTION. (To recreate this yourself you will need to
insert the same values in ARCHIVETB before attempting the insert)

      insert into CURRENTDATA (ID, ColA, ColB) values(99, 'Val1', 'Val2')

I am going to work around this but it will be interesting to get comments.

You should definitely be getting a better error message.

I've submitted a simplified repro, and will let you know when I hear
something back...

Is there a better way to do this very simple query(s)?
SQL HELP
INSTEAD OF INSERT TRIGGER AND NULL
Trigger Help
readinf blob data in sql trigger