Instead of insert trigger on view containing identity column

I have an 'instead of insert ' trigger an a view containing multiple tables,
one of the table-columns i use within the view is an identity-column.

Trying to do an insert into query on the view without the identity-column (funcid) results in:
insert into passw_functie (userid, functie, dienst, niveau) values(1, 'AGT', '6609', 1)
Server: Msg 233, Level 16, State 2, Line 1
The column 'funcid' in table 'passw_functie' cannot be null.

Providing the identity-column in the insert query results in:
insert into passw_functie (funcid,userid, functie, dienst, niveau) values(0,1, 'AGT', '6609', 1)
Server: Msg 16924, Level 16, State 1, Procedure functie_insert, Line 22
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

So how do i ever insert anything into this view?
I'm using sql-server 2000.

The DDL for the view is:
CREATE VIEW dbo.Passw_functie
AS
SELECT dbo.Toegang.ID AS funcid, dbo.Toegang.userID, dbo.Functie.functie, dbo.Dienst.Medical_Unit AS dienst, dbo.Toegang.niveau
FROM dbo.Toegang INNER JOIN
dbo.Dienst ON dbo.Toegang.dienstID = dbo.Dienst.ID INNER JOIN
dbo.Functie ON dbo.Toegang.functieID = dbo.Functie.ID INNER JOIN
dbo.[User] ON dbo.Toegang.userID = dbo.[User].ID

And for the table containing the identity column:
CREATE TABLE [dbo].[Toegang] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[userID] [int] NOT NULL ,
[functieID] [int] NOT NULL ,
[dienstID] [int] NOT NULL ,
[niveau] [int] NOT NULL ,
[deleted] [bit] NOT NULL
) ON [PRIMARY]
Thanks alot in advance,
Bart

[2171 byte] By [DogGuts] at [2008-2-16]
# 1
Your 2nd statement should work. You need to pass a dummy value for the identity column if SET IDENTITY_INSERT is OFF. You probably have some other error in your code. Please look at the Books Online topic mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_08_35pv.htm for more details. If this doesn't solve your problem, create a sample script with the necessary objects and post it since we cannot execute the code that you posted due to other missing objects.
# 2
Aoch,
this is embarrassing, indeed the second statement works, after i corrected a mistake in the 'instead of insert'-trigger.
To support multiple rows insert i used a cursor (performance is not an issue), to loop through the 'inserted'-table, where the query for the cursor returned 5 columns and i had only 4 variables to store into.
So you were right about having some other error in my code.
I only had read the error message Tongue Tied
Server: Msg 16924, Level 16, State 1, Procedure functie_insert, Line 22
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
Guess these things tend to happen on mondays...

thanks alot Big Smile

DogGuts at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...

SQL Server

Site Classified