Instead of insert trigger on view containing identity column
I have an 'instead of insert ' trigger an a view containing multiple tables, Trying to do an insert into query on the view without the identity-column (funcid) results in: The DDL for the view is: And for the table containing the identity column:
one of the table-columns i use within the view is an identity-column.
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.
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
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

